Search Date/Time Field Using Date Only

[gray]

Hi All

Windows XPPro
Access 2002/2007

I've a series of tables with Date_Added & Date_Last_Updated fields; they are in Date/Time format.

Although it's handy to have hh:m:ss times recorded they are only really useful for display purposes. When it comes to searching for records 'created or updated since mm/dd/yyyy', I only want to search on the date portions.

Yikes! I had no idea what I'd just wandered into!!

I build SQL and apply it as a recordsource to a form using VB.

I originally used the BETWEEN #mm/dd/yyyy# method but it didn't return records that had time portions.

I then thought I'd use the LIKE #mm/dd/yyyy construct but same problem, didn't return records with time portions.

I then tried xxx >= #mm/dd/yyyy# AND xxx <= #dd/dd/yyyy# - great until searching on one day only!

I then came up with BETWEEN #mm/dd/yyyy 00:00:01# and #mm/dd/yyyy 23:59:59# - works fine but daren't risk missing records that are created at exactly 00:00:00 (Murphy's Law).

Anyone know how to do this reliably? - I know Date/Time is ultimately stored as a double so I've a horrible feeling I'm going to have to search on the integer parts of the date - and suffer the consequent syntax jollies!

Thanks

 

[raskew]

Hi -

Lookup the DateValue() function, which will return only the date portion from a date/time field. For example:

between [B]DateValue[/B]([your d/t]) and [B]DateValue[/B]([your d/t]) + 1

HTH - Bob

 

[Brianwarnock]

Bob's correct, thought I'd get that out of the way, , but I was puzzled by the 01 second in the date/time here

then came up with BETWEEN #mm/dd/yyyy 00:00:01# and #mm/dd/yyyy 23:59:59# - works fine but daren't risk missing records that are created at exactly 00:00:00 (Murphy's Law).

Why not just

BETWEEN #mm/dd/yyyy# and #mm/dd/yyyy 23:59:59#

Brian

 

[gray]

Hi Bob and Brian

Thanks very much for the replies... I've dablled and I like em' both ... !!

Guess it hadn't ocurred to me that dates started at 00:00:00 hours ... it's an interesting dilemna... is 00:00:00 the start of one day or the end of the last?

The nice thing about Bob's DateValue solution is that I discovered its companion, 'TimeValue', so I guess with those one can do almost any combination of searches.

Very much appreciate the help! Thanks!

 

[Brianwarnock]

LOL
In the world of microsoft midnight is the start of a newday, whereas in the real world I guess we would think of the new day as starting at 1 second after midnight. Theirs is simpler for computers I think.

Brian