Getting Just the Date part of Date/Time field

[GaryElwood]

I am trying to count the number of records in a table the have the current date. The date field (DateAdded) is stored as a Date/Time value. So id i use Date() in my query criteria i will never get a match.
Question: How, in a query, do i compare just the date part of a date/time field to my query criteria?
Thanks



Field: Expr1: Format([DateAdded],"mm/dd/yyyy")
Total: Count
Criteria: Date()

[Robeen]

Try something like this:

 
SELECT Count ([TableName].[TimestampFieldName]) As TotalRecordsToday
FROM TableName
WHERE (((Format([TableName].[TimestampFieldName],"mm/dd/yyyy"))=Date()));

Hope this helps!

[GaryElwood]

I tryed your code but got a count of 0. I have one record with todays date?

Query code:
SELECT Count ([DataChanges_Part2].[DateAdded]) As TotalRecordsToday
FROM DataChanges_Part2
WHERE (((Format([DataChanges_Part2].[DateAdded],"mm/dd/yyyy"))=Date()));

Multipe records in table [DataChanges_Part2] one with todays date.
9/28/2011 8:41:19 AM

[Robeen]

Your date is showing up as 9/28/2011?

In the test I set up the date is defaulting to 09/28/2011.
And since my format is mm . . . it is matching the way the dates exist in my table.

Is your Date field defined as a Date/Time field in the Table?

[GaryElwood]

Yes. The Data Type is set to Date/Time and the format is set to "short Date"

[Robeen]

Just for now - try this [I changed the Format to "m/dd/yyyy"]:

 
SELECT Count ([DataChanges_Part2].[DateAdded]) As TotalRecordsToday
FROM DataChanges_Part2
WHERE (((Format([DataChanges_Part2].[DateAdded],"m/dd/yyyy"))=Date()));

See if that gives you your count.

Then - see if you can remove your 'short date' format in your table - leave it blank - so that it reverts to the default date format [which I think should be mm/dd/yyyy].

You need all your dates to have a standard appearance [ie - 09/09/2011 . . . two digits for month & date & 4 digits for year . . .]

Otherwise that Where clause will not work unless your month is 10 or more and your date is the 10th of the month or more . . . .

Let me know.

[GaryElwood]

Thanks!
Everything seems to be working.
Thanks again for the help....

[Robeen]

What did you do?