Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    strwhere = in ('19','Thusday','Daily')
    3. In VBA code I build this query:
    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
    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:

    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')

    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,

  2. #2
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I see that the problem is simple. I spelled the word "THURSDAY" incorrectly. The query works now!

    I suppose this is one of those ID10T errors.

    Best regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •