[AccessD] Access 2002 SQL problem

It's probably the dates.  When building, and using a SQL statement in
code, 'formatting' the date is not necessary.

So instead of 'WHERE SomeDateField=#" &
Format(SomeDate,"somedateformat") & "# ..... more sql

Use: 'WHERE SomeDateField=#" & SomeDate & "# .... more SQL

This way, Access will use the actual date (as long as SomeDate is an
actual Date variable type), and you don't have to worry about the
various regional date formats.  By using the Format statement, you are
forcing the date data type into a string format, that then Access has to
reverse (using the regional date format).  Now, if your date is a
string, just use DateSerial in your SQL string (like #" &
DateSerial("2000","1","30") & "#) and again you're letting Access do the
work for you.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Johncliviger at aol.com
Sent: Saturday, October 03, 2009 5:14 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Access 2002 SQL problem

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
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.