VBA/SQL Between Dates

[Joseph]

Hi. Within VBA I am trying to pass through an easy query, but I am having a hell of a time with the between dates...

Here is the bit that is giving the business

Code: Select all

AND [datecreated] " & " Between #" & startdate & "#" & " And #" & enddate & "#;"

Code: Select all

sqlStr = "SELECT * FROM LO_ENDOFSHIFTSURVEY WHERE location_id='" & locationid & "' AND [datecreated] " & " Between #" & startdate & "#" & " And #" & enddate & "#;"

Error message:
Incorrect syntax near '#'

[datecreated] field is set to datetime property.

Any help would be greatly appreciated, I have looked all over. Hoping this is something simple for you guys!

Thanks!
Joe

[HansV]

By "pass through", do you mean that the query is to be executed in SQL Server?

[Joseph]

No, sorry Hans, bad choice of words. I'm using VBA within excel to query SQL server.

[HansV]

Does this work?

Code: Select all

    sqlStr = "SELECT * FROM LO_ENDOFSHIFTSURVEY WHERE location_id='" & locationid & _
        "' AND datecreated Between '" & Format(startdate, "yyyy-mm-dd") & _
        "' And '" & Format(enddate, "yyyy-mm-dd") & "'"

[Joseph]

Yes. It. Does!

Thanks Hans!!!!!

[HansV]

SQL Server treats dates as strings; it's different from Access in this respect.

[Joseph]

Seeing the correct syntax is putting this together for me.

How would I copy the recordset WITH the field names as column headers? Currently using this bit but does not include.

ws2.Range("a2").CopyFromRecordset myRecSet

[HansV]

You have to do that separately, after opening the recordset:

Code: Select all

    Dim i As Long
    For i = 1 to myRecSet.Fields.Count
        ws2.Cells(1, i).Value = myRecSet.Fields(i - 1).Name
    Next i

[Joseph]

Perfect, thanks again Hans. I've just started my attempt to learn the methods of querying this way, so I may be back soon...as google fails me.