Date vs DateTime selection in Query

[eacollie]

My database contains linked Oracle tables where the date
field is date/time. I have a calendar control on a form
where I want the user to select a date and press a button
to preview a report that contains the records for that
date.

Can someone help me with the formatting of the date? I've
tried adding the form's calendar control name to the
criteria in the query behind the report, but I get no
records. If I leave the calendar control name out of the
query criteria, I get all records for all dates (so I'm
pretty sure my problem is with the formatting).

Thank you in advance!

 

[John Viescas]

If you're doing this in code, are you surrounding the date value with #
delimiters? Also, if the Oracle data contains a time as well as a date, you
should consider formatting the predicate like this:

strFilter = "tblOracle.MyDate >= #" & Forms!MyForm!calDate & "# AND
tblOracle.MyDate < (#" & Forms!MyForm!calDate & "# + 1)"

DoCmd.OpenForm "MyForm", WhereCondition:=strFilter

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)