[AccessD] Access 2002 SQL problem

There we go John, I knew good old Gustav would have the answer.

All this heartache because the yanks don't know that dates are always
ddmmmyyyy.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 03 October 2009 14:12
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Access 2002 SQL problem

Hi John

It's the date expressions in SQL. These must be formatted in the "reverse"
US-format or (preferred) after the ISO standard:

"WHERE ((tbl_PatientSpecs.RP_DATE Between #" & Format(SD, "yyyy\/mm\/dd") &
"# And #" & _ 
Format(ED, "yyyy\/mm\/dd") & "#) AND (tbl_PatientDetails.RecallFlag =
False));"

/gustav


>>> Johncliviger at aol.com 03-10-2009 12:14 >>>
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 as 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));"



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com