Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2010
    Posts
    33

    query results different on different computers

    I've tried to troubleshoot this problem but with no success. When I run a query I created on my computer the calculations work and the query runs as it should. When someone else runs the same query from the same shared database on our intranet there is a calculation error. Two different people have tried running the query on their machines and they both get the same error. Let me know if more information is required. If anyone has a suggestion I'm all ears!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    can we see the query
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    check the front ends are talking to the same back end (insert a record, say in 'you'r PC and make certain that record is visible in the others.

    make certain the query isn't dependant on any local data

    atfer tht asMyle says we'd probably need to see the query
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    Quote Originally Posted by BigToe View Post

    ...When someone else runs the same query from the same shared database on our intranet...
    Is this app split into a Front End/Backend configuration, as it has to be, for a multi-user environment? And if the answer is 'yes,' does each user have a copy of the Front End on their hard drives? The only thing that should be shared, here, is the Backend, which should only contain the Tables.

    What is the Access version? Is the version/service pack app the same in the development machine and the errant machines?

    Oh, yeah, we probably really, really, really need to see the query!

    Linq ;0)>
    Last edited by Missinglinq; 01-19-13 at 16:34.
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2010
    Posts
    33
    At the risk of exposing my admin flaws I will say that I do not have this Access 2007 database file split into a frontend/backend setup. It is used only by 4 trusted users and there has never been a problem with reports or queries with anyone opening this DB in the past. There are two backups of this DB and I did write the query on my personal backup file and exported it to the working file - if that helps provide any insight.
    SQL is as follows:
    SELECT [client first name] & " " & [client last name] AS [Name of Provider], [Client Information].MRN, Format(CDate([year] & "/" & [month] & "/" & [days].[days]),"yyyymmdd") AS [Service Date2], Round(4*IIf(IsNull([client information].[days of service per month]),([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[days],([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[client information].[days of service per month]),0)/4 AS DayHrs, Format(Now(),"yyyymmdd") AS [Extract Date], Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days, [Client Information].[Days of Service per Month]
    FROM days, [Client Information] INNER JOIN [Client Payments] ON [Client Information].[Client Number] = [Client Payments].[Client Number]
    WHERE (((days.days)<=Day(DateSerial(Year([forms]![clientinfo].[parisDate]),Month([forms]![clientinfo].[parisDate])+1,0))) AND (([Client Payments].Year)=[forms]![clientInfo].[CRyear]) AND (([Client Payments].Month)=[forms]![clientInfo].[CRmonth]) AND (([Client Information].Status)="active") AND (([Client Payments].[Hrly Rate])>0));
    thanks for the help!!!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    so have you checked that all users are using the same DB, ie new rows entered in one DB appear in all other DB's and vice versa

    Your current approach is OK ASSUMING that the db is not used as a multi user db concurrently. allowing multiple users to work concurrently in a single MDB/MDE is asking for trouble. if it is a multi user DB then split the application into a separate DB AND ideally deploy a separate front end to every user
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2010
    Posts
    33
    yes, I've been on the phone with one coworker and we went through a step by step opening the same shared drive etc and I also walked through the query with him confirming he was looking at the same SQL as me. The error occurs here:
    Round(4*IIf(IsNull([client information].[days of service per month]),([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[days]
    I used to have the "/31" instead of "/[days]" and even though the I confirmed on the phone that my coworker was looking at "/[days]" in this formula, the query acts like it is still dividing by 31.

    If it's not obvious [days] is an expression that counts the number of days in a field on a form and I use that number in the formula above.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    so what is the value of days

    as ever you need to examine the actual SQL NOT the VBA creating the SQL

    when you say its on a shared drive how is the share mapped/specified. is it a URL or a drive letter. Have you checked that new rows entered in one machine are seen ion all other machines AND vice versa. ie proving that they are the same db

    Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days,

    it could be a fragment of pasting the SQL but that looks wrong....


    assuming that all users are using the same DB then next step is to actually examine the SQL and see if there are differences. either set a watch/breakpoint or use a message box to display the ACTUAL SQL
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2010
    Posts
    33
    the value of [days] is the number of days in the month which is taken from a date field on the user's form. The hours alocated for each client per month is divided by [days] to give a breakdown of the number of hours of service per day. Each month will have a slightly different figure depending on if the formula gets divided by 30, 31, 28 or 29.

    it is on a shared drive letter.

    I haven't entered any new data to confirm if we are seeing the same database because I've walked through the process of opening the database on the shared drive with my coworker (i.e. not clicking on any shortcuts etc.)

    yes, "Mo nth" is from pasting on the forum. it's actually "Month" in the SQL. I also got rid of the second days.days. I was probably trying something and forgot to delete it.

    I may try deleting the query in the shared database and create a new query, pasting the same SQL and saving as a different name just to see if there is some weird link to my backup database and the shared one from exporting the query as I did. I believe the shared database was open by others the same time I was exporting. Not sure if that's a problem or not :-P

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    OK so check the shared drive maps to the same physical location
    right now you need to prove that everyone is using the same db
    again you need to take a step back from what the code shoudl be doign as opposed to what it is actually doing

    so by asking what the value of days is, Im not asking what it should be, but the actual value taken form the form is and comaparing against both result sets.

    withe respect
    I haven't entered any new data to confirm if we are seeing the same database because I've walked through the process of opening the database on the shared drive with my coworker (i.e. not clicking on any shortcuts etc.)
    doesn't prove anything. put some new records in on your machine and find out if your colleague can see them. if they can then defacto you are looking at the same DB.


    yes, "Mo nth" is from pasting on the forum. it's actually "Month" in the SQL. I also got rid of the second days.days. I was probably trying something and forgot to delete it.
    ..actually you want to get rid of two of the days
    Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days,


    is this db split into a front end and a separate back end?
    it could be a symptom of corruption (try a compact and repair on a backup and see if that helps
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536

    Exclamation

    Quote Originally Posted by BigToe View Post

    ...It is used only by 4 trusted users and there has never been a problem with reports or queries with anyone opening this DB in the past...
    You need to understand that having multiple users, opening the app concurrently, can work without problems for extended periods of time, but once corruption arises, it will become chronic! Corruption can take on many appearances, none of them pleasant, and can include permanent loss of data! Just to repeat, if more than one user is in this database at any given point in time, it simply has to be split! It doesn't matter whether you're speaking of two users or two hundred, and their Access skills makes no difference!

    BTW, the longest period of one of these running, non-split, without problems, is twelve years! But once the problems started, they kept arising, over and over and over again until the developer heeded advice and split the app!

    Linq ;0)>
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Apr 2010
    Posts
    33
    corruption is such an ugly word - especially when it's bolded! However, point taken. I will start the process of splitting the database. If I find a solution to the initial problem I will post. I do appreciate the help!

Posting Permissions

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