Query on a date (1 Viewer)

[KEKeogh]

OK So I have a Query that returns a date/time result.

I'm sure this is simple and probably asked before but I cannot find or think of the solution.

See the attached image

What I want is the criteria for the query to prompt for the date and when the user only types in "12/17/2015" it returns all records for that date regardless of the time.

Thanks
Kathie

 

[plog]

In Design view make a calculated field like so:

DateCriteria: DateValue([YourDateFieldHere])

Then in the criteria section put this:

[Enter Date]

 

[KEKeogh]

Doesn't seem to work.

Keep getting the errors

"Data type mismatch in criteria expression"

or

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

 

[plog]

Can you post your database? Or at least the SQL?

 

[KEKeogh]

The Query's SQL is:

SELECT Client_Contact_Info_Alternate.DateUpdated, "Contact" AS Expr1, DateValue([DateUpdated]) AS DateCriteria
FROM Client_Contact_Info_Alternate
WHERE (((DateValue([DateUpdated]))=[Enter Date]));

 

[plog]

Sounds like you have Null values in DateUpdated.

I think this will work if you first build another query to exclude nulls. Like so:

SELECT Client_Contact_Info_Alternate.DateUpdated, "Contact" AS Expr1 
FROM Client_Contact_Info_Alternate
WHERE DateUpdate Is Not Null;

You would save that naming it like 'QueryName'. Then you would use the query you have but replace the table name with 'QueryName':

SELECT DateUpdated, Expr1 
FROM QueryName
WHERE DateValue([DateUpdated])=[Enter Date];