how and when to use delimiter # for date criteria

[masoud_sedighy]

i am a bit confused about using # for date criteria in query. what i know, when I enter hard coded date in design query, i have to use # at first and of date in criteria for example #4/24/2016#

also i have see in some books in functions use # with form controls and some times in parameter query do not use # with form controls in criteria like below pictures.





please help how and when i have to use # in criteria of design query or VBA.

 

[CJ_London]

It all depend on your datatype - if you are comparing two date types, you do not need to use the #, but if you are comparing a date with a text value (such as when entered in a form then you a) use the # and b) the date needs to be in the US shortdate format - mm/dd/yyyy or written e.g. 4th March 2016.

SELECT * FROM myTable WHERE myTable.StartDate="#" & forms!myfrom!startdate & "#"


With parameter queries you predeclare the datatype e.g.

PARAMETERS myform!form!startdate datetime;
SELECT * FROM myTable WHERE myTable.StartDate=forms!myfrom!startdate

Alternative you can convert your text to a date using the CDate function

SELECT * FROM myTable WHERE myTable.StartDate=CDate(forms!myfrom!startdate)