Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am running several queries, some of which could have null values. I am getting an #error message when the values are null and then further queries/reports based on this query results in an overflow error.

    Here is what I have:

    SELECT Count([RESTRAINT Tally].AlphaID) AS Total, DCount("Result","RESTRAINT Tally","Result='Yes'") AS RESTRAINTYes, DCount("Result","RESTRAINT Tally","Result='No'") AS RESTRAINTNo, DCount("Result","RESTRAINT Tally","Result='QI'") AS RESTRAINTQI, DCount("Result","RESTRAINT Tally","Result='Yes'")/Count([RESTRAINT Tally].AlphaID) AS RESTRAINTYesRate, DCount("Result","RESTRAINT Tally","Result='No'")/Count([RESTRAINT Tally].AlphaID) AS RESTRAINTNoRate, DCount("Result","RESTRAINT Tally","Result='QI'")/Count([RESTRAINT Tally].AlphaID) AS RESTRAINTQIRate
    FROM [Restraint Tally];


    I have tried putting the Nz function in several places, but continue to get the #error. The Rate fields are percentages. Does this make a difference?

    Help!

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

    SELECT Count([RESTRAINT Tally].AlphaID) AS Total, DCount("Result","RESTRAINT Tally","Result='Yes'") AS RESTRAINTYes, DCount("Result","RESTRAINT Tally","Result='No'") AS RESTRAINTNo, DCount("Result","RESTRAINT Tally","Result='QI'") AS RESTRAINTQI, DCount("Result","RESTRAINT Tally","Result='Yes'")/Nz(Count([RESTRAINT Tally].AlphaID),1) AS RESTRAINTYesRate, DCount("Result","RESTRAINT Tally","Result='No'")/Nz(Count([RESTRAINT Tally].AlphaID),1) AS RESTRAINTNoRate, DCount("Result","RESTRAINT Tally","Result='QI'")/Nz(Count([RESTRAINT Tally].AlphaID),1) AS RESTRAINTQIRate
    FROM [Restraint Tally];

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No. I'm still getting #error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In that case, we'll have to see a stripped down and zipped copy of the database. Remove sensitive information from the copy.

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't have access to the tables and this database is enormous!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this query work? It's not the one you want. but it might give a clue.

    SELECT Count(*) AS Total, Abs(Sum([Result]="Yes")) AS RESTRAINTYes, Abs(Sum([Result]="No")) AS RESTRAINTNo, Abs(Sum([Result]="QI")) AS RESTRAINTQI
    FROM [Restraint Tally];

Posting Permissions

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