[RESOLVED] Handling Dates with VB 6.0 and MS Access

[JarheadJoe]

I have read all of the tutorials and demos I could locate on the Forum regarding how to properly handle dates in VB 6.0. I am still having a problem using a SQL statement from within my program to properly retrieve records based on selecting a "Beginning and Ending Date" from a MS Access 2003 db.

I have an Access table where one of the fields "EligibilityDate" is the Data Type of "Date/Time".

If I use the below code, the program does work properly and returns the records I am expecting:

Adodc1.recordSource = "SELECT MSIS, FirstName, LastName, SSN, DOB, EligibilityDate FROM Table1 WHERE EligibilityDate BETWEEN #07/01/1990# AND #07/16/2009#"

Adodc1.Refresh

However, I want to use the DTPicker to allow the user to select a Beginning and Ending date for the query.

If I use the code below, I get a "Data Mismatch" error.

Adodc1.recordSource = "SELECT MSIS, FirstName, LastName, SSN, DOB, EligibilityDate FROM Table1 WHERE EligibilityDate BETWEEN '" & Format(DTPicker1.Value, "mm/dd/yyyy") & "' AND '" & Format(DTPicker2.Value, "mm/dd/yyyy") & "'"

Adodc1.Refresh

I have tried unsuccessfully to read the all tutorials on how to properly handle dates but, I cannot get this to work. I am missing something, please help.

Thanks.

[RhinoBull]

Instead of single quote in your dynamic query use the pound sign (#) just like you did in the sql that worked for you.

[JarheadJoe]

Thank you. Silly mistake. I appreciate your response. It worked!