How write a correct SQL request from Excel to Access without error?

Question

I want to run an SQL query from excel to Access as shown below. When I run the code, I get an "Incompatible data type in criterion expression" error. Do you have a tip, please, to get me out of this?

Regards

Sub test()
  Dim myDate As Date
  Dim myCUID As String
  Dim i As Long
  Dim cnt As Object, rst As Object

  Set rst = CreateObject("ADODB.Recordset")
  Set cnt = CreateObject("ADODB.Connection")

  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.Path & "\ATD_monitoring_basePro.accdb';"

  'Open connection to the database
  cnt.Open strConnection

  'Begin transaction processing
  cnt.BeginTrans

  myDate = WorkSheets("Bla").Range("A1").Value
  myCUID = "XXXX1234"
  sqlCmd = "SELECT COUNT(*) FROM Table1 WHERE Date= '" & myDate & "' AND CUID='" & myCUID & "';"
  rst.Open sqlCmd, cnt

  'Close the ADO objects
  cnt.Close
  Set rst = Nothing
  Set cnt = Nothing
  On Error GoTo 0

End Sub

maybe WHERE [Date] Also, what is myDate being returned as? — Nathan_Sav
– Nathan_Sav, Commented Apr 14, 2021 at 7:26
In Access "Date" field is also string? from my view you probably in Access have normal date format, and now you trying to compare string vs date. — bankeris
– bankeris, Commented Apr 14, 2021 at 7:45
@Nathan_Sav. myDate retrieves a value from a range with the Date category of type "*14/03/2012". The problem arose when I changed the text range to Date. it worked well before — Christophe C.
– Christophe C., Commented Apr 14, 2021 at 7:48
@Bankeris. In Access Date field is also in Date format not in String. — Christophe C.
– Christophe C., Commented Apr 14, 2021 at 7:50

Kostas K. · Accepted Answer · 2021-04-14 08:00:13Z

1

In Access, dates must be wrapped with # and formatted mm/dd/yyyy.

Try this:

sqlCmd = "SELECT COUNT(*) FROM Table1 WHERE [Date]= #" & _
          Format(myDate, "mm/dd/yyyy") & "# AND CUID='" & myCUID & "';"

Debug.Print sqlCmd

answered Apr 14, 2021 at 8:00

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

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

1 Comment

Christophe C. Over a year ago

Many thanks. it works much better like that. This one I will remember well.

Collectives™ on Stack Overflow

How write a correct SQL request from Excel to Access without error?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related