Date Criteria in a Query

[Misty]

The [wostatus].[changedate] field is formatted as a Date/Time field. In the below query, I need to put [What Date?] as criteria in the [changedate] field. I want the user to type in only the date in mm/dd/yy format. Can this be done?

----Start SQL----

SELECT dbo_wostatus.status, dbo_wostatus.wonum, dbo_wostatus.changedate, dbo_wostatus.changeby
FROM dbo_wostatus INNER JOIN dbo_workorder ON dbo_wostatus.wonum = dbo_workorder.wonum
WHERE (((dbo_wostatus.status)=[What Status?]) AND ((dbo_wostatus.changeby)="Q123456"));

----End SQL----

 

[sfreeman@co.mer]

Not sure if this will work as I could not test, but off the top, try this and see if it helps....

SELECT
dbo_wostatus.status
, dbo_wostatus.wonum
, [Enter Date Here:]
, dbo_wostatus.changeby

FROM
dbo_wostatus

INNER JOIN
dbo_workorder ON dbo_wostatus.wonum = dbo_workorder.wonum

WHERE
(((dbo_wostatus.status)=[What Status?])
AND ((dbo_wostatus.changeby)="Q123456"));

 

[Misty]

Date format in the table:

6/3/2004 10:13:01 AM

--------------------
I tried what you suggested and the query resulted in an empty recordset.

I can't figure out how to get the query to look at just the date part and NOT consider the time.

 

[Mile-O]

Use the DateValue() function

 

[Misty]

Use the DateValue() function

I've never used the DateValue Function before so I did a search in this forum and found another time you suggested it. I set it up as:

SELECT dbo_wostatus.status, dbo_wostatus.wonum, dbo_wostatus.changedate, dbo_wostatus.changeby
FROM dbo_wostatus INNER JOIN dbo_workorder ON dbo_wostatus.wonum = dbo_workorder.wonum
WHERE (((dbo_wostatus.status)=[What Status?]) AND ((dbo_wostatus.changeby)="Q123456") AND ((DateValue([dbo_wostatus].[changedate]))=[What Date?]));


and it works just great!

Thank you so much for your advice!