Finding records in a query where Date/time has been used

[Frankie_The_Flyer]

Hi Accessers!
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss.
I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria)
Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat.
I've also tried various machinations of "Like" and "Between" without success. (I want the user to only have to enter one date)
I've searched the Internet but can't seem to find anything that answers this problem.
Any help please??

 

[Galaxiom]

You can use
WHERE DateValue([datefield]) = [Enter Date]

However this means the query must apply the function to every record in the table so it will get slower and slower as the database grows.

Better to input using a texbox on a form and use

WHERE [datefield] BETWEEN Forms!formname.textbox AND Forms!formname.textbox + 1

 

[CazB]

you ARE storing your date and time in a date field, aren't you? and not a text field?

 

[Frankie_The_Flyer]

Thanks Galaxiom; I'll try that on Tuesday when I get back into the office. I've used a list for another function of the db and was considering using a text box to drive this, but still needed a steer as to how to achieve the result. (Relatively new at this game!):banghead:

And yes CazB, the date column is set at Date/Time / Medium Date in the originating table. Actually I (think!) I proved that by putting Date() in the criteria of the query in the date column and didn't get a Data Mismatch warning as I did with the CDate column. (CDate does do as it suggests it should though in that it returns a dd/mm/yyy in the CDate column)

 

[gemma-the-husky]

except, if you extract dates between today and tomorrow, then you will also get tomorrows records, where there is no time set, wihch you said may be the case.

ie

between #16 aug# and #17 aug#

gives you everything up to and including #17-aug 00:00:00#

----
you could try >=checkdate and <(checkdate+1)

 

[Frankie_The_Flyer]

That seems to work thanks to all.