Search code examples
sqlvbaexceldata-connections

Microsoft Query in Excel VBA - how to pass date filters

I am really hoping someone here could help me with the issue I have spent hours trying to fix with no result.

I am trying to establish a data connection with a csv file using MS query in Excel VBA. I need to filter the data out from the csv file into the spreadsheet by applying a date filter on a certain column. When the date is fixed (i.e. hardcoded in VBA), the connection works absolutely fine. However, I would like the date to be a user input and that's where I am facing problems. Basically, I am not sure how to pass a date variable to the connection.

When the macro works fine, the SQL statement looks like this:

.CommandText = "SELECT * FROM " & csvName & " WHERE SECTYPE='GS' AND LAST TRADED DATE={ts '2016-01-29 00:00:00'}"

When I try to pass the date via variable sValnDate, I get 'SQL syntax error':

.CommandText = "SELECT * FROM " & csvName & " WHERE SECTYPE='GS' AND LAST TRADED DATE={ts " & sValnDate & "}"

I have tried several configurations of the variable. I have tried to pass it as a date, a string exactly as in the correct command, a date formatted as required in the correct command, keeping and removing the curly brackets with each format of the variable etc, but nothing worked.

I have just presented here 1 statement to keep things simple. However, if you need to see the entire block (not more than 15-20 lines), please let me know.

Thanks in advance

PS: just looked at the preview. Somehow `` around LAST TRADED DATE have been removed here.

Solution

  • Assuming that sValnDate is a string that looks like 2016-01-29 00:00:00 then you are simply missing the ticks (aka single quotes or Chr(39)).

    .CommandText = "SELECT * FROM " & csvName & _
               " WHERE [SECTYPE]='GS' AND [LAST TRADED DATE]={ts '" & sValnDate & "'}"

    If sValnDate is an actual date then format it like,

    .CommandText = "SELECT * FROM " & csvName & _
               " WHERE [SECTYPE]='GS' AND [LAST TRADED DATE]={ts '" & _
               Format(sValnDate, "yyyy-mm-dd hh:mm:ss" & "'}"