Access 2002 SQL Problem

[johnb]

Hi all

I have the following SQL code snippet and it produces the incorrect number
of records when its run by Docmd.RunSQL strSQL, but the correct number when
run thru Access query. No errors are raised. I've been looking at this
problem for several days on/off now so can anyone offer a word of wisdom?

TIA
john cliviger


Public Sub LetterHistory(R As String, SD As Date, ED As Date) ' R = report
name, SD = Start Date, ED = End Date
Dim strSQL As String
On Error GoTo Flag1
strSQL = "INSERT INTO tbl_Letter_Specs ( Refnoc, DateLetterSent,
LetterName ) " & _
"SELECT tbl_PatientDetails.REFNOC, Date() AS DateLetterSent, '" & R & "'
AS LetterName " & _
"FROM tbl_PatientDetails INNER JOIN tbl_PatientSpecs ON
tbl_PatientDetails.REFNOC = tbl_PatientSpecs.REFNOC " & _
"WHERE ((tbl_PatientSpecs.RP_DATE Between #" & Format(SD, "dd/mm/yyyy") &
"# And #" & _
Format(ED, "dd/mm/yyyy") & "#)AND ((tbl_PatientDetails.RecallFlag) = 0));"

 

[Stefan Hoffmann]

hi John,

I have the following SQL code snippet and it produces the incorrect number
of records when its run by Docmd.RunSQL strSQL, but the correct number when
run thru Access query. No errors are raised. I've been looking at this
problem for several days on/off now so can anyone offer a word of wisdom?

Use Debug.Print strSQL and grab the generated SELECT part. But into a
query. Check your date condition carefully. Try using

Format(SD, "dd/mm/yyyy") <= tbl_PatientSpecs.RP_DATE
AND tbl_PatientSpecs.RP_DATE <= Format(SD, "dd/mm/yyyy")

As Jet uses internally US formated dates use

Format(SD, "#yyyy/mm/dd#")

instead.


mfG
--> stefan <--