Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A query on queries (Access2000/Access 2003)

    Hi,
    Has anyone been having problems with queries? I have a client who is getting invalid results from a query. The following is the query:
    PARAMETERS [FORMS]![frmBldMeals]![SlipEndDte] DateTime, [FORMS]![frmBldMeals]![SlipStartDte] DateTime;
    SELECT tblClient.DietSiteCode, tblClient.RouteCode, Forms!frmBldMeals!SlipEndDte AS SlipEndDte, tblClient.LastName, tblClient.FirstName, tblClient.TransNo, Forms!frmBldMeals!SlipStartDte AS SlipSDte, tblDietSite.DietSiteName, tblRoute.RouteDesc, tblClient.Sex, tblClient.RaceCode, tblClient.LivesCode, tblClient.OtherDesc, tblClient.Handicapped, tblClient.Ambulatory, tblClient.StatusCode, tblClient.NRScore, tblClient.MOW, tblClient.HealthCode, tblClient.ResAddr, tblClient.CityTownCode, tblClient.ResZip5, tblClient.ResZip4, Month(Date()) AS DateMo, Month([BirthDte]) AS BirthMo, DateDiff("yyyy",[BirthDte],Date()) AS CltAge, IIf([DateMo]>[BirthMo],[CltAge],[CltAge]-1) AS ClientAge, tblClient.BirthDte, tblClient.SSN, tblClient.SSNSuff, tblClient.AreaCode, tblClient.PhoneNo, tblClient.CellAreaCode, tblClient.CellPhoneNo, tblClient.ServiceCode, tblClient.OrgDte AS SServDte, tblClient.EServDte, tblClient.EntitlementCode, tblClient.Medicaid, tblClient.DietCode, 0 AS HotMeal, 0 AS BagMeal, 0 AS ExtraMeal, tblClient.ClientNumber, tblClient.Assign, tblClient.EmerName1, tblClient.EmerPhone1, tblClient.EmerCellPhone1, tblClient.EmerName2, tblClient.EmerPhone2, tblClient.EmerCellPhone2, tblClient.Limit, tblClient.Protected, tblClient.RedeDte, 0 AS FundingSource, True AS CanUpdate, tblClient.OrgDte
    FROM (tblDietSite INNER JOIN tblRoute ON tblDietSite.DietSiteCode = tblRoute.DietSiteCode) INNER JOIN tblClient ON (tblDietSite.DietSiteCode = tblClient.DietSiteCode) AND (tblRoute.RouteCode = tblClient.RouteCode)
    WHERE (((tblClient.StatusCode)="A") AND ((tblClient.MOW)="Y") AND ((tblClient.OrgDte)<[FORMS]![frmBldMeals]![SlipEndDte]));
    The Client keeps getting records where the StausCode is "C", in the selection, and I can NOT see how. HELP!!!!!!
    Sarge

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

    Re: A query on queries (Access2000/Access 2003)

    You say that
    <hr>The Client keeps getting records where the StausCode is "C", in the selection<hr>
    Have you verified that this is actually happening?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A query on queries (Access2000/Access 2003)

    Hans,
    I have seen the results from the client running on theirmachine, however, so far I have not been able to reproduce it on my machine. I thought, a bug in their copy of Access or that they missed an office update. They are running from an MDE and the data tables are on three separate password protected MDBs. I hate chasing ghosts, so I do not let the client have the passwords or the MDBs for the MDEs.
    Still Kornfused,
    Sarge

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

    Re: A query on queries (Access2000/Access 2003)

    It seems rather unlikely that a missed update would cause a query to select records incorrectly.

    Does the problem occur consistently on the client's machine?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A query on queries (Access2000/Access 2003)

    Hans,
    As I said I have not been able to recreate the problem on my machine. The only different being is I run from the MDB, while they run from a MDE. The other maddening thing is that does not happen all the time on the client's machine. This seems to be hit or miss. I am almost to the point of making the selection by status code its own query and pass its output to another query with all the other functions.

    Sarge ( going slightly mad )

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

    Re: A query on queries (Access2000/Access 2003)

    I also doubt that .mdb or .mde would make a difference, but it might be worth the effort for you to test on the .mde file on your PC.

    Does the problem PC have significantly less RAM than your PC? Or does the client have a very large number of other apps running simultaneously? Dangerously low resources might be the cause - this could also explain why the problem occurs intermittently.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A query on queries (Access2000/Access 2003)

    Hans,
    That I can not answer. But she does have E-Mail open all day, and is usually in Word or Excel. I usually just run access and the windows 99,000 apps. I probably have more memory.

    Thanks,
    Sarge

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A query on queries (Access2000/Access 2003)

    If e-mail is done in Outlook, you can count on your RAM being hogged up by it (especially 2k3). Outlook leaves open threads to documents opened through emails, even after the email has been closed...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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