Results 1 to 4 of 4
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    #NUM! error or error telling me my query doesn’t include the specified expression


    This query is evaluating the responses in the column and applying a value based on the response. Then it divides the sum of the values by the count of how many responses were provided giving us a score for the column.The problem I am running into is, there may not be a response for each employee in every column, so I am getting #NUM! errors. I have tried the following formula and various other versions of the same but either I still get the #NUM! error or I get an error telling me my query doesn’t include the specified expression.
    Code:
    Sum(IIf([AutoCoverage]="","",IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0)))))/IIf([AutoCoverage]="","",Count([AutoCoverage]))
    Code:
    Sum(IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0))))/IIf([AutoCoverage]="",0,Count([AutoCoverage])
    Code:
    Sum(IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0))))/IIf([AutoCoverage]=0,0,Count([AutoCoverage])
    I basically just want the cell left blank if the field does not have a response.Thank you for any help you can provide on this!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you allowing empty string in field? I don't allow empty string in fields so I would test for Null with IsNull() function or use Nz() to provide alternate value or use Is Null.

    Can't divide by an empty string. Use Null. Arithmetic with Null returns Null.

    Review http://allenbrowne.com/QueryPerfIssue.html and http://allenbrowne.com/casu-12.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Thanks June7. The table is used by 3 separate forms depending on the type of work an employee does. So there will always be empty or blank fields for every employee. I built it this way because I was told that it was less efficient to have 3 tables and 3 forms and would cause my database to run slower.I am not sure how to modify my current query to include Nz though. Can you help point out how I would write that?

  4. #4
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    I got it figured out! In case others have a similar issue here is what I did:
    Code:
    Sum(IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,IIf([AutoCoverage]="No",0,Null)))))/Count(IIf([AutoCoverage] Is Null,"",[AutoCoverage]))

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

Similar Threads

  1. Replies: 13
    Last Post: 05-31-2019, 10:48 PM
  2. Replies: 2
    Last Post: 11-05-2018, 11:40 AM
  3. Replies: 1
    Last Post: 06-14-2015, 09:48 AM
  4. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Tags for this Thread

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