user7219039 user7219039 - 10 days ago 6
SQL Question

SQL Access VBA Excel

I got this code:

SQLQ = "SELECT * FROM blabla WHERE bsn = '" &
Format(ThisWorkbook.Sheets("QueryResult").Range("B1").Value,
"#########000000000") & "' AND datum= '#" &
ThisWorkbook.Sheets("Initiƫle berekening").Range("C6").Value & "#'"
Debug.Print SQLQ
objRs.Open SQLQ, con, adOpenStatic


When I run this I get an error from VBA.
"Data type mismatch in criteria expression" (Error -2147217913(80040e07))

With the debug.print I check if it gets the right values and it does so I don't get what I do wrong..

Answer

You will also need to format the date expression:

 SQLQ = "SELECT * FROM blabla WHERE bsn = '" &
 Format(ThisWorkbook.Sheets("QueryResult").Range("B1").Value, "#########000000000") & "' AND datum= #" &
 Format(ThisWorkbook.Sheets("Initiƫle berekening").Range("C6").Value, "yyyy\/mm\/dd") & "#"