Correct Formatting of SQL in VBA?

[Coffee]

Does anyone know how i would get this to work. Searching between 2 input dates. having trouble sorting out the commas and ## etc. ;/

strSQL = "SELECT dbo_HistoricalReport.MessageNo, dbo_HistoricalReport.DateTime, dbo_HistoricalReport.UserName, dbo_HistoricalReport.FCS, dbo_HistoricalReport.KKSCode, dbo_HistoricalReport.Message " & _
"FROM dbo_HistoricalReport " & _


"WHERE dbo_HistoricalReport.DateTime" >= & strStartDate & <= & strEndDate & _
";"

[pbaldy]

Try

"WHERE dbo_HistoricalReport.DateTime Between #" & strStartDate & "# And #" & strEndDate & "#"

[Coffee]

thanks pbaldy. i now have the following code:

"WHERE dbo_HistoricalReport2.DateTime BETWEEN #" & Format(strStartDate, "dd/mm/yyyy") & "# AND #" & Format(strEndDate, "dd/mm/yyyy") & "#" & _

this is currently working, but for some reason it will only return results between the 7th and 30th of each month. and wont display any results if you search for the 1st of any month or 2nd, 3rd, 4th, 5th, 6th. i am stumped ;/

[pbaldy]

VBA assumes dates are in US format, so try formatting them that way (mm/dd/yyyy). All kinds of functions and stuff on this:

http://access.mvps.org/access/datetime/date0005.htm

http://allenbrowne.com/ser-36.html

[Coffee]

hmm tried all that its the correct format and still wont produce the correct result.

the date is imported as dd/mm/yyyy
the date is stored in table as dd/mm/yyyy
the excel default for date format for date is dd/mm/yyyy

the sql query 'between dd/mm/yyyy and dd/mm/yyyy' works fine

that same query put in a sql string in vba and submitted returns incorrect results..

go figure? o.0

[pbaldy]

From the first link:

VBA assumes automatically that any dates being used follow US Settings

from the second:

In VBA code...Access expects these literal dates to be in the American format

In other words, none of what you mentioned matters. When you build a string in VBA, you need to use US format (or another, the specifics of which elude me right now).

[Coffee]

yep you were right i had to convert the input into American format and then search the database to produce the right results.

cheers mate!