Results 1 to 10 of 10
  1. #1
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6

    Query results not correct in Access, correct in Excel

    Hi All

    I am an Access newbie. I am running Access 2003, using linked tables from another database called MAILman ( a database used by not-for-profit organisations in Australia). When I run a query in Access e.g. APPEAL = "XMAS2010" And DONORTYPE = "Donor Current", the results that appear after I press the Run Query button are not correct (e.g. a donor type other other than "Donor Current and/or an appeal other than XMAS2010 will appear in the results) ... but when I export the results as an Excel spreadsheet, the search results are correct. It seems I am doing something wrong ... but what?



    Onesimus

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Would you post the SQL for your Access query please?

  3. #3
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6
    Hi RuralGuy

    The SQL as requested:

    SELECT dbo_A_CurrentContactTypes.ContactTypeDescription, dbo_A_Contact_ContactDetails.FName, dbo_A_Contact_ContactDetails.LName, dbo_A_Contact_ContactDetails.CompName, dbo_A_Contact_ContactDetails.Town, dbo_A_Contact_ContactDetails.State, dbo_A_Contact_ContactDetails.PostalCode, dbo_Donations.ActivityCode, dbo_Donations.DonationValue
    FROM (dbo_A_CurrentContactTypes INNER JOIN dbo_A_Contact_ContactDetails ON dbo_A_CurrentContactTypes.ContactId = dbo_A_Contact_ContactDetails.ContactID) INNER JOIN dbo_Donations ON dbo_A_CurrentContactTypes.ContactId = dbo_Donations.ContactID
    WHERE (((dbo_A_CurrentContactTypes.ContactTypeDescriptio n)="Donor Current") AND ((dbo_Donations.ActivityCode)="TAX2011"));

    The result inside Access did show an Activity Code other than TAX2011 - but the spreadsheet result was correct.

    Onesimus

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Nothing unusual in the SQL that I can see. Is the system patch level up to date?

  5. #5
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6
    Quote Originally Posted by RuralGuy View Post
    Nothing unusual in the SQL that I can see. Is the system patch level up to date?
    Couldn't say RuralGuy as both the Access and MAILman databases are stored on the server of the company that makes the MAILman database! Given that the exported (Excel) results are OK, then I can live with incorrect eresults being displayed in Access.

    Onesimus

  6. #6
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6
    Found a solution: Records > Refresh. Unfortunately, it only refreshes records that are visible in the results screen. If the results are small, you can enlarge the results screen before using Records > Refresh; otherwise, you will need to display un-refreshed images several times and refresh several times.

  7. #7
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6

    How to auto-refresh query results.

    I am using Access 2003 to search an external database dynamically (via ODBC). When I run my query, I get updated results - but these need to be refreshed. I use Records > Refresh to refresh the results, but is there a way for Acess to auto-refresh the results?

    I find that when when I refresh records, Records > Refresh only affects the results that are visible. Where I know the number of records to be found is small, I enlarge the results screen before using Records > Refresh. Otherwise, for a large number of found records, I need to display non-refreshed records before using Records > Refresh. I may need to do this several times if the number of records found is large.

    Onesimus

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So you are not running this query from a form, you are just looking at the Datasheet view of the query?

  9. #9
    Onesimus is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    6
    Hello RuralGuy

    Correct RuralGuy! I am only using query capabilities of Access to query the MAILman database because of the limited search capabilities of the MAILman database (a database used by charities in Australia). To be honest, the fact that Records > Refresh does update the found records for me is OK (even if I have to do it several times). I just find it somewhat surprising that Records > Refresh does not refresh all records found as a result of running a query!

    Cheers from the Land Downunder!

    Onesimus

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you used a form you could do a Me.Recordset.MoveLast and then Me.Recordset.MoveFirst to fully populate the Recordset after the query runs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access does not build correct APPEND query
    By lwoods in forum Queries
    Replies: 3
    Last Post: 05-06-2011, 02:19 AM
  2. Replies: 6
    Last Post: 01-07-2011, 12:50 PM
  3. dcount syntax correct?
    By lbgtp in forum Reports
    Replies: 2
    Last Post: 12-29-2010, 10:01 AM
  4. What is the correct syntax for
    By giladweil in forum Access
    Replies: 1
    Last Post: 07-29-2010, 04:56 AM
  5. Returning correct rows in a join/nested query
    By goneaccessing in forum Queries
    Replies: 5
    Last Post: 03-03-2010, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums