Parameter Query using Excel Variable

[DreamerZ]

I posted this to the Access Query SQL Jet forum too. Thought it might be good here as well.
--------------------------

I have a simple select query in Access with no parameters. I have recorded the following code to automatically get the desired data from the Access query:

Dim strDate As String

'{ts '2004-12-02 00:00:00'}
strDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss;@")

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access 97 Database;DBQ=G:\d-mist\MisDB\data\mis.mdb;DefaultDir=G:\d-mist\MisDB\data;DriverId=25;FIL=MS Access;MaxBufferS" _
        ), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT BST_PAC_Job.FK_SHIFT, BST_PAC_Job.Accounts, BST_PAC_Job.AccountsOK, BST_PAC_Job.FK_JOB_TYP, BST_PAC_Job.FK_SYSTEM, BST_PAC_Job.shiftdate" & Chr(13) & "" & Chr(10) & "FROM `G:...`.BST_PAC_Job BST_PAC_Job" _
        , _
        "" & Chr(13) & "" & Chr(10) & "WHERE (BST_PAC_Job.shiftdate={ts '[b]2004-12-02 00:00:00[/b]'}" & Chr(13) & "" & Chr(10) & "ORDER BY BST_PAC_Job.FK_SHIFT" _
        )
        .Name = "Query from MS Access 97 Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery = False
    End With

The above code work perfectly. What I want, however, is a variable date (the bold section) using the strDate variable.

I have tried:

.shiftdate={ts '" & strDate & "'}" &...

But I get a SQL Syntax error. Hovering over strDate upon debugging shows the proper date variable and format as in the bold section above with double quotes around the data ("2004-12-02 00:00:00") which I think is the problem.

So, what am I doing wrong? I'm pretty sure it's the formatting of the variable code, but I can't get it to work.

Thoughts? TIA.

DreamerZ

DreamerZ

 

[PHV]

You may try this:
"WHERE (BST_PAC_Job.shiftdate=#" & strDate & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DreamerZ]

When it errors now, it highlights the last line

.Refresh BackgroundQuery = False

for the SQL Syntax error.

When it recorded, the line actually read

.Refresh BackgroundQuery:= False

but changing it to that doesn't work nor does commenting the line out.

I'm not very familiar with this Add type of coding, so I'm not sure where to look for help on the formatting of it all.

Thoughts?



DreamerZ

 

[DreamerZ]

The problem is not with the refresh line. I went back to a hard-coded date and it worked fine with either refresh format.


DreamerZ

 

[DreamerZ]

Alright, well, I'm not sure why this worked and the other ways didn't, but it's fixed now.

I took the whole "WHERE..." line and put it in a string variable. I then creating put the date variable inside that new string variable and then put the string variable in the SQL code.

It works fine.

intDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss;@")
strDate = "WHERE (BST_PAC_Job.shiftdate={ts '" & intDate & "'})"

Not sure why it worked that way and not the other, but at least it works now.


DreamerZ