CODEWITHSUNDEEP
sqlexcelvba
Angelika
Angelika

Reputation: 216

Excel vba sql: Syntax error in date inside query string

I'm trying to get some data with sql-filter WHERE inside Excel Macros. But I get some errors with data-types:

Dim cn As Object, rs As Object, output As String, sql As String

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

Dim Value As Date
Value = .Range("B4").Value ' 30.10.2020

sql = "SELECT * FROM [Page 1$] WHERE DateTime = #" & Format(Value, "dd\.mm\.yyyy") & "#"
Set rs = cn.Execute(sql) 'here I get error

DateTime cells have format "Date". I get Run-time error Syntax error in date in query expression 'DateTime = #30.10.2020'.

Original file is here.

What am I doing wrong with format?

Upvotes: 1

Views: 632

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider parameterization using the ADO Command object and specify your input parameter as adDate type. Also, usually, proper dates are separated by hyphens and not periods between date parts (day, month, and year). But your system region/locale can be different assuming the cell is not a Custom format.

Dim cn As Object, cmd As Object, rs As Object
Dim output As String, sql As String
Dim dt As Date
Const adCmdText As Integer = 1, adDate As Integer = 7, adParamInput As Integer = 1

'dt = .Range("B4").Value                              ' IF REGION USES PERIODS IN DATES
dt = CDate(Replace(.Range("B4").Value, ".", "-"))     ' IF REGION USES HYPHENS IN DATES

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" _
                        & ThisWorkbook.FullName & ";" _
                        & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

' PREPARED STATEMENT WITH QMARK ? PLACEHOLDER
sql = "SELECT * FROM [Page 1$] WHERE DateTime = ?"

Set cmd = CreateObject("ADODB.Command")

With cmd
  .ActiveConnection = cn
  .CommandText = sql
  .CommandType = adCmdText

  ' BIND PARAM AND DEFINE TYPE AND LENGTH
  .Parameters.Append .CreateParameter("prm", adDate, adParamInput, , dt)

  ' CREATE RECORDSET
  Set rs = .Execute
End With

Upvotes: 1

Related Questions