Expert: Robert Nunemaker - 1/9/2009

Question
Hi,

I am using Microsoft Access as a database and VB 6 as programming language.


In Access I have a field of type "DateTime" in which there is data containing both Data and Time e.g. 9/1/2008 11:40 , 9/1/2008 12:15 , 9/2/2008 3:35 , 9/3/2008 9:45 etc.

I want to select data between dates. If I use BETWEEN ... AND , it is returning 0 records. Please help me how I can retreive data between dates having all the time coming between in these dates.

Looking forward for your prompt reponse.

Regards,
Mohsin

Answer
Your dates need to be wrapped in "#" symbols if they are true Date or Date/Time fields.  Example:

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset

db.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\myFolder\myDB.mdb;Uid=Admin;Pwd=;"
db.Open
rs.Open "Select ID, InitJoinDate From myTable Where InitJoinDate Between #01/01/1990# And #12/31/2008#", db, adOpenForwardOnly, adLockReadOnly
Do Until rs.EOF
   ' Do your logic here
   rs.MoveNext
Loop
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing