Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Query returns different answers (Access 97)

    Access database has 3 linked odbc tables from sql server.
    Other tables are resident in the db for month names/sequence numbers etcetera, nothing bigger than 100 records/5 fields

    Query/query results in report format.

    On my desktop the results are complete, with all expected data returned.
    On colleagues desktop (same setup re:OS/application etcetera) the reports are intermittently correct/not correct.

    Anyone else experienced this?

    TIA
    Alan
    Cheshire
    UK

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query returns different answers (Access 97)

    You'll need to fill in more details before anyone can answer. What does correct/not correct entail? Are there error or system messages? Are the reports only partially printing? Is there any indication of network difficulties on that machine?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    I would compare resident tables espesially those aren't big.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    odbc table contains many records of invoices and their values. Each invoice is referred to by invoice number (primary key unique) and each record has a tax year and tax period and client number.
    The local tables are months: where the tax period is related to a month number and month name, this is essentially a lookup table
    and sequences: where there is a list of period numbers and years against a sequence which allows me to sort the results any way up.

    I know for a given client number that I should have invoice values from November 2003 up to and including December 2004.

    On the report which is run from my desktop, all of the months and values are returned i.e. Nov 2003 through to December 2004

    On a colleagues desktop, sometimes the report returns all of the values, other times it returns only up to November 2004.

    Late today we had the situation where he rang me and he had the report missing December 2004 whilst at the same time I then ran report and it returned all of the values.

    Any suggestions?

    TIA
    Alan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returns different answers (Access 97)

    Grasping at straws here: is it possible that people have different date settings in Windows (US date format vs UK date format)?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query returns different answers (Access 97)

    Is it possible that the colleague has a different Jet service pack installed? I've seen even weirder behavior result from that.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    Hmmmm.. couple of things to go at there:-
    Date - the date for the desktop is set each time user logs on, but that wouldn't explain why the report gave full list one minute and part list the next.
    Jet service pack - easy way to check?

    Thanks
    Alan

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returns different answers (Access 97)

    MSKB article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine explains how to determine which Jet service pack has been installed.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query returns different answers (Access 97)

    Since you are working with Access 97, you may also have an issue with the ODBC driver for SQL Server. For one thing, you may need to know what version of SQL Server is being used. In addition, you are working with Jet 3.x with Access 97 so you may want to search for the latest version of that, as well as making sure you have all of the 97 Service Packs installed. Hans' suggestion about checking the date settings of each PC is also a good possibility.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    Latest:
    Just going to check the Jet engine versions (thanks guys for the links) but message received saying:
    "when I open the database and run the report it works fine. But when I try to run the report a second time, then the data comes back incomplete.
    The workaround is to open the database, run the report, close the database...open the database, run the next report, close the database...etcetera"

    WOW, tht's a lotta work for someone today running reports

    Alan

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query returns different answers (Access 97)

    Can you duplicate that behavior on your PC?
    Wendell

  12. #12
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    So far I have been unable to repeat the behaviour on my desktop.

    Results are in from Jet versions:
    My desktop: 4.0.8618.0
    Errant desktop: 4.0.6218.0

    Does anyone know the significance of the later version and what the fixes (if any) there were?

    Thanks
    Alan
    Cheshire
    UK

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query returns different answers (Access 97)

    The specific fixes don't matter as much as matching the two machines. It's one of the first things to do when you get errant behavior on a machine.
    Charlotte

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query returns different answers (Access 97)

    In this case, I don't think the version of Jet 4.0 is an issue - we are apparently dealing with Access 97 which should be using Jet 3.51. Or am I confused? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Wendell

  15. #15
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Query returns different answers (Access 97)

    I just checked the number of records in invoice table and it is currently 172,000 rows. Could this be an issue or is size unimportant?


    Other than that, ODBC driver is 03.85.1025

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

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