2

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.

Improve this question

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this query line was what ended up working for me querydate = "(DTG > '" & Format(inputdate, "YYYY-MM-DD") & "')" Thank you!
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") & "')"
Improve this answer

Comments

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'"
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.