We help IT Professionals succeed at work.

Operations Admin

103 Views
Last Modified: 2018-02-02
I'm using a simple dcount function to count records in the query with certain criteria. My problem is, I am trying to use an IIF function in part of the dcount criteria. I can’t seem to get it right. My Expression reads as…….Access 2010

Error_Num: IIf([TypeCA]="Verbal","Error_3",IIf([TypeCA]="Written","Error_6",IIf([TypeCA]="Final","Error_9","Error_" & DCount("CorrectiveAction","tblErrorData","EM_ID=" & [EM_ID] & "And ID<=" & [ID]))))
 
Expr1000      CorrectiveAction      Source      Error_Num      ID      EM_ID      TypeCA
10477      9/19/2016      Quality      Error_3      673      10477      Verbal
10477      11/2/2016      Quality      Error_2      835      10477      
10477      11/18/2016      Quality      Error_3      887      10477      
10477      12/6/2016      Quality      Error_6      930      10477      Written
10477      12/30/2016      Quality      Error_5      956      10477      
10477      1/9/2017      Quality      Error_6      970      10477      
10477      3/10/2017      Quality      Error_9      1092      10477      Final

I am close but I need it to show the following, what am I doing incorrectly?
 
Expr1000      CorrectiveAction      Source      Error_Num      ID      EM_ID      TypeCA
10477      9/19/2016      Quality      Error_3      673      10477      Verbal
10477      11/2/2016      Quality      Error_4      835      10477      
10477      11/18/2016      Quality      Error_5      887      10477      
10477      12/6/2016      Quality      Error_6      930      10477      Written
10477      12/30/2016      Quality      Error_7      956      10477      
10477      1/9/2017      Quality      Error_8      970      10477      
10477      3/10/2017      Quality      Error_9      1092      10477      Final
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Removed SQL Server topic and added MS Access topic.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Seems correct to me. Something else is going on.
Could you provide the table for testing?

/gustav
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Jim Dettman (Microsoft MVP/ EE MVE) (https:#a42050940)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.