Trouble with query criteria for Date/time to return results for "Today"

[123seven]

Hi
I have a date/time field ( "DateAdded") on a data entry form and want the recordset of that form to only show the current day's records. IF I set the query criteria for the forms record source to:

>=#17/03/2012#
(today being that date), I get today's records. That's what I want but don't want to create a parameter query asking for that date every time the form closes/opens during the day.

Date()
returns no results and I'm wondering if it has to time with the time component.



>Date()-1
Returns yesterday's and todays records.

Is there a simple date function that will work here?

Thanks for your time.

[June7]

If your field does have time component saved, such as 3/12/2012 2:37:00 PM, then yes the filter needs to deal with that. Construct a field in query (or calculated field of table) that drops the time part, like: CDate(Format(fieldname, "m/d/yyyy")). Then apply the Date() function as criteria to this field.

[123seven]

Hey June7
thanks for your quick reply. Here's what happened - added new column and expression:

DateOnly: CDate(Format([DateAdded],"dd/mm/yyyy"))

returns shortdate, same as [Date Added], was feeling very good at this point, then added the criteria:

=Date()

and got a "Data Type Mismatch in Criteria Expression"

also tried a new column/expression:

Expr1: DateValue(Format([DateAdded],"Short Date"))

which returned records in shortdate format as expected. Added the =date() criteria and got the same error msg -- Also can't figure how to see datatype of a calculated field (if that's possible)...thanks again

[June7]

I just tested my suggested expression and it works for me. Wait, you have format of d/m/yyyy, not m/d/yyyy. m/d/yyyy is Access convention. Review http://allenbrowne.com/ser-36.html

The Format function results in a string so using CDate or DateValue to define a date.

Is your field name [DateAdded] or [Date Added] - with or without space?

[123seven]

Hi June7,
Just wanted to thank you and let you know how this was resolved. I reviewed the Allen Browne article as you suggested and through this worked out my problem. I had two records with null values in the date/time field - To quote Allen Browne :
"Calculated Date Fields

Access can also misinterpret calculated date fields in queries, especially where the date format is not American and the field contains some Nulls. The obvious symptoms are that the field is left-aligned and sorted as strings."

So the nulls as well as the 'Canadian' regional date setting (which is actually ok to use in the query grid (if that's your regional setting, according to Al)) were the trouble. Once I cleared up the null values both calculated fields returned results when queried with the =date() criteria.

Thanks again!