VBA variable in SQL date query

Question

I'm trying to query the SQL database for all lines where the date is after a date given through user input. I've run into a variety of errors from "incorrect syntax near" when I surround my date with "#" to "arithmetic overflow error converting expression to". My current code looks like this:

inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
Debug.Print inputdate

querydate = "(DTG > " & Format(inputdate, "MMDDYYYY") & ")"

select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

DTG is the column name for the date-time group in the SQL database. Any idea where I am going wrong? I've tried every solution I could find over the past few days without luck. Thank you in advance.

Brett Wolfington · Accepted Answer · 2015-08-07 17:44:07Z

1

The primary issue is that dates must be enclosed in single-quotes. Here is a complete working example (minus a valid connection string) that should explain how to achieve your objective. Note that you will also want to switch to the ISO date format, in which the order is Year-Month-Day (YYYY-MM-DD).

Sub UpdateRecords()
    Dim connection As New ADODB.connection
    Dim recordset As New ADODB.recordset
    Dim connectionString As String
    Dim query As String
    Dim inputdate As Date
    Dim querydate As String

    inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
    querydate = "(DTG > '" & Format(inputdate, "yyyy-mm-dd") & "')"
    query = "select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

    connectionString = "..."        
    connection.Open connectionString
    recordset.Open query, connection
    ActiveSheet.Range("A1").CopyFromRecordset recordset
End Sub

answered Aug 7, 2015 at 17:44

Brett Wolfington

6,6374 gold badges35 silver badges52 bronze badges

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

1 Comment

jsnarski Over a year ago

this query line was what ended up working for me querydate = "(DTG > '" & Format(inputdate, "YYYY-MM-DD") & "')" Thank you!

Don Jewett · Accepted Answer · 2015-08-07 23:22:32Z

1

Dates for SQL Server should be formatted as a date or date/time, qualified with single-quotes:

Date in ISO unseparated date format

querydate = "(DTG > '" & Format(inputdate, "yyyymmdd") & "')"

Date/Time in ISO 8601 format

querydate = "(DTG > '" & Format(inputdate, "yyyy-mm-ddThh:mm:ss.000") & "')"

edited Aug 7, 2015 at 23:22

answered Aug 7, 2015 at 17:36

Don Jewett

2,00715 silver badges29 bronze badges

Comments

James Groves · Accepted Answer · 2015-08-07 17:38:54Z

0

Date format for MySQL is YYYY-MM-DD. Also, as DTG is datetime, you'll need DATE(DTG) > DATE(NOW()) for example - DATE() is a MySQL function that checks only the date portion of a datetime stamp.

Your query should look like this:

querydate = "(DATE(DTG) > " & Format(userinput, "YYYY-MM-DD") & ")"

select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

answered Aug 7, 2015 at 17:38

James Groves

711 silver badge4 bronze badges

Collectives™ on Stack Overflow

VBA variable in SQL date query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related