SQL Access VBA Excel

Question

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..

Try to remove the single quotes around the date (next to the #). — Vincent G
– Vincent G, Commented Nov 28, 2016 at 8:09

Gustav · Accepted Answer · 2016-11-28 08:22:40Z

1

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") & "#"

answered Nov 28, 2016 at 8:22

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Access VBA Excel

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related