I’m getting different results from the same query and am unable to figure out why. Here are the particulars:
1. MS Access 2003 SP3 database.
2. User form with user selections make this WHERE clause
[indent] strwhere = in (’19’,’Thusday’,’Daily’)
[/indent]
3. In VBA code I build this query:
[indent] strSQL = “SELECT Report_to_Automate.Contact_Email_Address, ” & _
“Report_to_Automate.Report_Name, ” & _
“Report_to_Automate.HospitalGroupID, ” & _
“Report_to_Automate.Frequency ” & _
“FROM Report_to_Automate ” & _
“GROUP BY Report_to_Automate.Contact_Email_Address, ” & _
“Report_to_Automate.Report_Name, ” & _
“Report_to_Automate.HospitalGroupID, ” & _
“Report_to_Automate.Frequency ” & _
“HAVING Report_to_Automate.Frequency” & strwhere
rsData.Open strSQL, dbs, adOpenKeyset, adLockOptimistic
[/indent]
NOW, here is the strange part.
In the immediate window, if I stop the code after the record set is created (rsdata.Open….)
and run rsData.RecordCount, I get 9 records. These only represent the ‘Daily’ part of my WHERE clause.
However, If I paste the strSQL into a query grid it looks like this:
[indent]SELECT Report_to_Automate.Contact_Email_Address, Report_to_Automate.Report_Name, Report_to_Automate.HospitalGroupID, Report_to_Automate.Frequency FROM Report_to_Automate GROUP BY Report_to_Automate.Contact_Email_Address, Report_to_Automate.Report_Name, Report_to_Automate.HospitalGroupID, Report_to_Automate.Frequency HAVING Report_to_Automate.Frequency in (’19’,’Thusday’,’Daily’)
[/indent]
Then I run the query, I get the correct number of rows including
‘Daily’, ‘Thursday’, and ’19’
Why would the same query string yield two different sets of results?
Please let me know if you want further specifics or table sample.
Thanks,
