Sql access, how to return between dates

Question

How to specify a date range in MS Access? Is the bottom query correct? Should I put "2/1/2010" in quotation marks? Or do I need to do something like date(2/1/2010) ?

 SELECT [Occurrence Number] as Fld FROM [Lab Occurrence Form] WHERE [Practice Code]="ACCIM" AND [1 0 Preanalytical (Before Testing)]="1.1 Specimen Mislabeled" AND ([Occurrence Date] Between 2/1/2010 and 2/28/2010); 

the following gives me a type mismatch

 SELECT [Occurrence Number] as Fld FROM [Lab Occurrence Form] WHERE [1 0 Preanalytical (Before Testing)]="1.1 Specimen Mislabeled" AND [Occurrence Date] between "1/1/2009" and "2/2/2010"; 

Answer 1

ms-access uses the Jet engine, which uses # for the date:

 SELECT Orders.* FROM Orders WHERE Orders.OrderDate Between #3/1/96# And #6/30/96#; 

Answer 2

 AND ([Occurrence Date] Between #2/1/2010# and #2/28/2010# 

This is how you specify Access to interpret something as a date.

Answer 3

The Ms access database uses "#" to represent dates. Therefore, if you want to write 12/13/2013 as acceptable for MS, then you should write it as # 12/13/2013 #.

An example sql query for a table called "test" with two field identifiers and a date.

select * from the test, where date = # 12/13/2013 #.

Sample SQL query for vb.net 2008 to search for database records between two dates

"select * from info_session, where i_date between #" and startdate and "# and #" and enddate and "#"

Answer 4

The dates used in select (at Microsoft) are defined as: "#" + month + "/" + day + "/" + year + "#"

field day is number 01.02 ---, 31
field month - number 01.02 ... 12
field year 2014, 2015 ... etc.

you can build a SQL field dynamically

es. in vbscript

 dt1="#"&month(date1)&"/"&day(Date1)&"/"&year(Date1)&"#" dt2="#"&month(date2)&"/"&day(Date2)&"/"&year(Date2)&"#" 

and then you can use the select in the SQL field

where in the Orders table the field OrderDate x is indicated.

 SQL="select * from Orders where OrderDate Between " & dt1 & " and " dt2 

now you can access the database