Search by Date only

[mavisyew]

Hi All,

I have a question below.

I have a field in my table which is Date/time.
It contain the date/time but when I search, i only want to search by the Date. It should not look at the time.
I have a form created which the user will select the date and run the query.

How can i do so?
What is the SQL code to add in the query?

The one i enter is



WHERE((TBLCaseDetail.ReportedDateTime)=Format([Forms]![FormFindCase]![DateSelection],"dd/mm/yyyy"));

Thanks in advance.

[June7]

Try:

WHERE Format([ReportedDateTime],"dd/mm/yyyy") =

Review https://www.accessforums.net/access/...ate-46496.html

[mavisyew]

Hi June7,

Thanks for replying.
I use the code below but is not working.

Format([Forms]![FormFindCase]![ReportedDateTime],"dd/mm/yyyy") =[Forms]![FormFindCase]![DateSelection]

[mavisyew]

Hi June7,
Sorry my code is below instead.

Format(TBLCaseDetail.ReportedDateTime,"dd/mm/yyyy") =[Forms]![FormFindCase]![DateSelection]

[June7]

What does 'not working' mean - what happens?

Did you review the referenced link discussing the issue of using non-U.S. date?

[mavisyew]

Hi June,

What i mean is that i have a data which is for that date but when i run the query it does not pull out.

I have read the link but dont really understand.

[mavisyew]

Hi All,

Anyone can help please?

I changed my data type for my date to Short Date and long date all does not help.
I have a form which with a date selection with a button to run the query.

My Query as below.

SELECT TBLCaseDetail.CaseID, TBLCaseDetail.Status, TBLCaseDetail.ReportedDate, TBLCaseDetail.Type, TBLCaseDetail.Description
FROM TBLCaseDetail
WHERE (((TBLCaseDetail.Status)=[Forms]![FormFindCase]![StatusSelection]) AND #" & format([TBLCaseDetail.ReportedDate],"mm/dd/yyyy") & "# =[Forms]![FormFindCase]![DateSelection] AND ((TBLCaseDetail.Type)=[Forms]![FormFindCase]![CaseTypeSelection]));

[June7]

Try not using the # characters. The Format function converts the date value to a string and the input in DateSelection is a string. This will compare string with string and hopefully the non-U.S. date structure will not be issue.

[mavisyew]

Hi June7,

I got it.
Thanks!