asked on

Operations Admin

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

Vitor Montalvão

Removed SQL Server topic and added MS Access topic.

Gustav Brock

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

/gustav

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Howard

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