Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query for a range of dates (2000)

    I have 2 tables.
    Product contains the fields ProductID, ProductName and ProductCost.
    Promotion contains the fields ProductID, PromoStartDate, PromoEndDate and PromoPrice

    I want to create a query which allows a start and end date to be entered.

    Query should return one record for each date between reporting start and end date, displaying the ProductID, ProductName, date and either the ProductCost OR the PromoPrice applicable on that date.

    This should give me an array of dates from which i would choose the ones i require during the reporting.

    I'm stumped with how to produce a query listing the range of dates in such a way.

    Anyone able to point me in the right direction? Or offer any other suggestions as to how this should be done

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query for a range of dates (2000)

    To be entirely clear, do you want to see all products where a promotion was active during the specified period?
    If that's what you are after, you can use a parameter query which prompts you for the dates, and put exactly the same phrase in the criteria grid for both PromoStartDate and PromoEndDate, but in separate rows so you get an OR operation. The phrase should look something like:<font color=blue><font face="Georgia">
    Between [Enter Start Date] And [Enter End Date]</font face=georgia></font color=blue>
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for a range of dates (2000)

    I had tried the between [startdate] and [enddate] method, but the problem is more than just a simple selection process

    I want to have both those that are on promotion and those that aren't.
    If product A has a normal price of

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query for a range of dates (2000)

    Do you actually have a promotion record for each of those days? If not, you have a much more complex problem, as you need to synthesize data.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for a range of dates (2000)

    Products aren't on promotion all the time, so yes, I do need to synthesize data.
    I've started to look at the problem another way - using dlookup to find if a promo record exists for the date and using it if it does.

    I'm using:
    Promo: DLookUp("[PromoPrice]","ProductPromo","[PromoStart] = #" & [ReportDate] & "# And [ProductID]= '" & [ProductID] & "'") as one column of my query
    and
    pricetouse: IIf(IsNull([promo]),[productsellpricecase],[promo]) as the other

    I guess i could merge these together, but haven't yet.

    My problem now is that the date between the # marks doesn't work correctly.
    I'm using Uk date formats DD/MM/YYYY, but the dates between #'s seem to only work in US date format.

    I have a valid promotion for UK format 2/4/4 to 4/4/4 2nd April to 4th April, but it only matches if I use 4/2/4 (4th Feb in my language, but 2nd April in yours)

    Should I use some other means of date selection?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for a range of dates (2000)

    To get around the US/UK formatting problem, use

    ... #" & Format([ReportDate], "mm/dd/yyyy") & "# ...

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for a range of dates (2000)

    Thanks for that - are there any other areas where the uk/us date is a problem? Is this "feature" corrected in Access 2002/2003 - it would seem logical to make it conform to the international date settings on the computer. Or maybe there IS a practical reason for this??

    The full line of the promo query is now

    Promo: DLookUp("[PromoPrice]","ProductPromo","[PromoStart] <= #" & Format([ReportDate],"mm/dd/yyyy") & "# and [PromoEnd] >= #" & Format([ReportDate],"mm/dd/yyyy") & "# And [ProductID]= '" & [ProductID] & "'")

    Now returns the correct value for any date of promo

    I've got a little bit further to go with this query, but I think this has broken the back of it.

    Many thanks

    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for a range of dates (2000)

    I know this is confusing, since you can use localized formats in the Access interface.

    The Where Condition argument of functions such as DLookup is a string that is evaluated by the SQL parser, not by the Access interface. SQL requires literal dates and numbers to be in US format. Similarly, if you use literal dates or numbers in VBA code, they *must* be in US format too.

    I don't think this will be changed, so you'll have to live with it.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query for a range of dates (2000)

    Hans has sorted the issue with international date formats, so that looks to be under control. If you still want to synthesize records for each day based on products that are on promotion during the specified period, you will probably have to resort to VBA, and use recordset manipulation to actually create temporary tables. If you want to pursue that approach post back and we'll try to lead you through it - however it looks like you may have found an alternative approach.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for a range of dates (2000)

    Thanks Wendell - Hans was, as ever, most helpful. My train of thought changed as I was working through it and I need a fairly painless solution. I think I'll leave the synthesizing for a date just after hell freezes over!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query for a range of dates (2000)

    Sounds like a good plan to me - enjoy the ensuing sanity! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •