2

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.

M.L
  • 328
  • 2
  • 12
KMLN
  • 79
  • 2
  • 3
  • 14
  • Have you tried the following `cDate(sValnDate)` or `Format(sValnDate ,"dd-mmm-yy")` either of these may work as its possible that the variable containing the date is not adequately formatted. – izzymo Feb 15 '16 at 14:40
  • Thanks. Just tried both, with and without "{ts}", but none worked. If I use cDate without the curly brackets, `LAST TRADE DATE`=cDate(sValnDate) the macro does run, but doesn't fetch any records. – KMLN Feb 15 '16 at 14:51

1 Answers1

0

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" & "'}"