sql access how to return between dates

How do I specify a date range in MS Access? Is the below query correct? Do I have to put "2/1/2010" in quotes? Or do I have 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);

t开发者_如何学Gohe 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";

---

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

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

---

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

This is how you tell Access, to interpret something as date time.

---

Ms access database uses the "#" for presenting dates. So if you want to write 13/12/2013 as ms access acceptable form then you have to write it as #13/12/2013#.

An example sql query for table called "test" with two fields id, and date.

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

An example of a sql query for vb.net 2008 to find the database records between two dates

"select * from info_session where i_date between # " & startingdate & " # and # " & enddate & " #"

---

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

the field day is a number 01,02---,31
the field month is a number 01,02 ...12
the field year is 2014, 2015 ...etc

you can build the SQL field dinamically

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 a field SQL

where the table Orders has a field named OrderDate es.

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

now you can access the Database