Query for a range of dates (2000)

Topic

New Reply

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

Reply | Quote

Replies

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:
Between [Enter Start Date] And [Enter End Date]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

To get around the US/UK formatting problem, use

… #” & Format([ReportDate], “mm/dd/yyyy”) & “# …

Reply | Quote

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 [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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 [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

Reply | Quote

To get around the US/UK formatting problem, use

… #” & Format([ReportDate], “mm/dd/yyyy”) & “# …

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

Sounds like a good plan to me – enjoy the ensuing sanity!

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:
Between [Enter Start Date] And [Enter End Date]

Reply | Quote