Excel vba sql: Syntax error in date inside query string

Question

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?

have you tried Format(Value, "dd.mm.yyyy")?

MarEll
– MarEll

2020-10-30 14:06:55 +00:00
Commented Oct 30, 2020 at 14:06 — MarEll
– MarEll, Commented Oct 30, 2020 at 14:06
Yes, the same error

Angelika
– Angelika

2020-10-30 14:07:32 +00:00
Commented Oct 30, 2020 at 14:07 — Angelika
– Angelika, Commented Oct 30, 2020 at 14:07

Parfait · Accepted Answer · 2020-11-02 16:00:46Z

1

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

edited Nov 2, 2020 at 16:00

answered Oct 30, 2020 at 22:37

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

Angelika Over a year ago

for your code I get "Compile error: Expected: If or Select or Sub or Function ir Property or Type ir With or Enum or end of statement" on End cmd

Angelika Over a year ago

I changed End cmd on End With. Now I have error with .CommandType = adCmdText: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

Parfait Over a year ago

Sorry for those syntax errors. Yes, it should be End With and because your are running late binding (not early binding) of ADO objects, the constants adCmdText, adDate, adParamInput are not known. So, you need to manually define their values. See Constant declaration at top.

Collectives™ on Stack Overflow

Excel vba sql: Syntax error in date inside query string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related