[AccessD] Access 2002 SQL problem

Hi John,
Hope you are keeping well.

When you say " when its run by Docmd.RunSQL strSQL" 
do you mean the records are actually placed in the table 

AND 

when you say " number when run  thru Access query " 
do you mean just VIEWING them in the QBE?

If so, and the number in the table is less that the viewing total then it
may be an index on the table which is limiting them and maybe not throwing
the error or you code is trapping and ignoring it.

How many are written to the table?
How many are shown when viewed?
Have you isolated the differing records?

Some wild shots really.

Max

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Johncliviger at aol.com
Sent: 03 October 2009 11:14
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