Query Return Reocrds for only the current Date.

[gumby]

[Date] format 8/9/2006 8:27:55 AM

I would like to have a query that when I run it it only returns the
records that the current date. I do not want to prompt for date.


If I just use Date () as the critera I get no returns.

Thanks

 

[jahoobob via AccessMonster.com]

Date() should work. Did you place a space between Date and ()? You
shouldn't.

[Date] format 8/9/2006 8:27:55 AM

I would like to have a query that when I run it it only returns the
records that the current date. I do not want to prompt for date.

If I just use Date () as the critera I get no returns.

Thanks

 

[Tom Lake]

[Date] format 8/9/2006 8:27:55 AM

I would like to have a query that when I run it it only returns the
records that the current date. I do not want to prompt for date.


If I just use Date () as the critera I get no returns.

Thanks

If your date format includes time, you'd have to reformat it to
get rid of the time or else use Now() as a criterion. I still
doubt you'd get a hit, though, since the time would have to match, too.
I'd create a calculated field using the DatePart of your date then
compare to Date().

Tom Lake

 

[jahoobob via AccessMonster.com]

Just caught the format part. You will need to use something like dat:Format
(Date,"m/d/yyyy") as a field in your query and use Date() as th criteria.
Also, never, ever use a reserved word like Date as a field name. It can only
creat heartaches. Use EntryDate, BeginDate, or even Dater or Dat, but never
Date!
Date is a reserved word because it is the name of a function, Date().

Date() should work. Did you place a space between Date and ()? You
shouldn't.

[Date] format 8/9/2006 8:27:55 AM

[quoted text clipped - 4 lines]

Thanks

 

[John Spencer]

Your problem is that Date() is today's date at MIDNIGHT. So unless you have
a record with that exact date and time you won't get any records returned.


Try one of the following
OK (but cannot use any index on the field - with a lot of records - 20,000
or more - it can be slow)
Field: JustTheDate: IIF(IsDate(YourDateField),DateValue(YourDateField),Null)
Criteria: Date()

or this variant
Field: JustTheDate: Format(YourDateField, "Short Date")
Criteria: = Format(Date(),"Short Date")

Better (in theory could return records from tomorrow at midnight):
Field: YourDateField
Criteria: Between Date() and DateAdd("d",1, Date())

Even better (should not return records from tomorrow midnight):
Field: YourDateField
Criteria: >= Date() and < DateAdd("d",1, Date())

Date() should work. Did you place a space between Date and ()? You
shouldn't.

[Date] format 8/9/2006 8:27:55 AM

I would like to have a query that when I run it it only returns the
records that the current date. I do not want to prompt for date.

If I just use Date () as the critera I get no returns.

Thanks