The Old Joel on Software Forum: Part 4 (of 5)

Date Querying

Hello, i am a bit of an amateur and i was wondering if you could help me.

If todays date is say 5/2/03 and i want to find records with dates 3 days previously, how would i accomplish this. My initial thought was 3>[Please Enter Todays Date] but it did not work, any suggestions?

thanks

anon
Wednesday, February 25, 2004

based on what you've got, it appears you're working with some version of MS Access, where the [Please Enter Todays Date] pops up a dialog for a quick and dirty parameter input.

If that's true -- that you're using Access (it's been a while, so bear with me on this) you'll want to look in the help file for the DateDiff() function and the Date() function. It will end up looking something like:

DateDiff("d",[Please Enter Todays Date],Date()) <=3

TRANSLATION: give me all records where the difference in dates [DateDiff()] in increments of days ["d"] between the user entered data [Please Enter ...] and the current date [Date()] is up to, and including [<=] three [3] days.

Keep in mind a few things. This is Access bastardized SQL, it's in no way ANSI. It's using VBA functions in the WHERE clause [DateDiff() and Date()] -- so if you've got lotsa records it's going to perform like a dog. Jet basically has to do a table scan, and perform the calculation on *all* records to see if it meets your criteria. This is dog slow. If you've only got a few tens of thousands of records or less, it might be bearable. If you're up into a few hundred thousand or even across the million or so record boundary, prepare for a long coffee break before you run it.

YMMV

Sgt. Sausage
Wednesday, February 25, 2004

There's an easier way. Use

where order.shipping_date > Date() - 3

This will return all the orders who have a shipping date later than today's date, minus 3 days.

Yves
Wednesday, February 25, 2004

unfortunately neither, i am aware of the Date() function but i would like it to be manually entered date,

What i suspected might work but didn't may give you more of an insight

"date to be returned" > [please enter todays date] - 3

any suggestions?

anon
Wednesday, February 25, 2004

You're sure you want to request today's date from the user instead of grabbing it from the system? Oh well.

You can try

where order.shipping_date > CDate([Enter today's date]) - 3

CDate will attempt to convert an expression into a date.

Yves
Wednesday, February 25, 2004

do u mean

where loan.date_to_be_returned> CDate([Enter today's date]) - 3

in SQL or QBE ?

anon
Wednesday, February 25, 2004

Whatever happened to UseNet?

Grumpy Old-Timer
Wednesday, February 25, 2004