Complex Expression Access 2010

[mark6109]

Here is a challenging one for you guys, thank you in advance. The end goal of the below expression is to determine if a unit has passed or failed a repair process....Here is the original request from my colleague and below is what I have so far:

• Where there is a U anywhere in the Repair Code = FAIL
o Access Repair Code calculation = Like “*U*”

• Where there is an R anywhere in the Repair Code AND the Repair Comments field is NOT NULL = FAIL
o Access Repair Code calculation = Like “*R*”
o Access Repair Comments calculation = Is Not Null

• Where there is an N anywhere in the Repair Code = PASS
o Access Repair Code calculation = Like “*N*”

• Where there is an R anywhere in the Repair Code field AND the Repair Comments field is NULL = PASS
o Access Repair Code calculation = Like “*R*”
o Access Repair Comments calculation = Is Null

• Any code that does NOT contain a U, N or R anywhere in the Repair Code = PASS
o Access Repair Code = Not Like “*U*” and Not Like “*R*” and Not Like “*N*”
--------------------------------
My expression so far:
FAIL / PASS RESULT: IIf([REP_CODE]="*u*" Or [REP_CODE]="*r*" And [REPAIR_COMMENTS]=Not IsNull([REPAIR_COMMENTS]),"FAILURE",IIf([REP_CODE]="*n*" Or [REP_CODE]="*r*" And [REPAIR_COMMENTS]=IsNull([REPAIR_COMMENTS]) Or [REP_CODE]<>"u" Or [REP_CODE]<>"n" Or [REP_CODE]<>"r","PASS",""))

I think my issue lies withing the [field] = Not IsNull....

 

[plog]

Based on your methodology, every person will pass.

>>>Where there is an N anywhere in the Repair Code = PASS

>>>Any code that does NOT contain a U, N or R anywhere in the Repair Code = PASS

Logically it follows that if a record has an 'N' in it, it will pass. If it doesn't have an 'N' it that record will also pass. Therefore, all records will pass.

Can you better explain your criteria? Is there a criteria precedence? For example, check all the failure options and if they haven't failed check the pass options?

 

[mark6109]

Based on your methodology, every person will pass.

>>>Where there is an N anywhere in the Repair Code = PASS

>>>Any code that does NOT contain a U, N or R anywhere in the Repair Code = PASS

Logically it follows that if a record has an 'N' in it, it will pass. If it doesn't have an 'N' it that record will also pass. Therefore, all records will pass.

Can you better explain your criteria? Is there a criteria precedence? For example, check all the failure options and if they haven't failed check the pass options?

Hi Plog,
Thanks for your feedback. That is correct, anything with an "N" repair code should pass. And>>>Any code that does NOT contain a U, N or R anywhere in the Repair Code = PASS <--This is correct.
I think the problem is here. [REPAIR_COMMENTS]=IsNull([REPAIR_COMMENTS])
Is there a better way to write this? I don't think the = symbol should be in between the IsNull statement.

 

[plog]

Your criteria is so complex and the nested IIF statement isn't helping. My suggestions is to create a VBA function that you pass the [REP_CODE] and [REPAIR_COMMENTS] to.

In your query, you would put this:

FAIL / PASS RESULT: getPassFail([[REP_CODE], [REPAIR_COMMENTS])

Then you would create a function in a Module that would run through all the possiblities like this:

Function getPassFail(rcode, rcomments)
Dim ret As String 
ret = ""
' by default it will be blank
If (repcode Like "*U*") Then ret = "FAILURE"
If (repcode Like "*R*") And (IsNull(rcomments) = False) Then ret = "FAILURE" 
' checks for failure
If (repcode LIKE "*N*") then ret="PASS"
If (repcode LIKE "*R*") AND (isnull(rcomments)) then ret="PASS"
If (repcode NOT LIKE "*N*") AND (repcode NOT LIKE "*R*") AND (repcode NOT LIKE "*U*") then ret="PASS" 
' checks for passing
getPassFail = ret 
' returns result
End Function

There are a few problems with my code:

1. It will always return "PASS" because of the 'N' issue I mentioned prior.
2. Its possible for a record to first fail, and then pass. If a record has a 'U' and an 'N' in it, it will first fail because of the 'U' and then pass because of the 'N'

So if you could explain the precedence of the criteria (which to do first, second, third, etc) I could correct my code.

 

[mark6109]

Your criteria is so complex and the nested IIF statement isn't helping. My suggestions is to create a VBA function that you pass the [REP_CODE] and [REPAIR_COMMENTS] to.

In your query, you would put this:

FAIL / PASS RESULT: getPassFail([[REP_CODE], [REPAIR_COMMENTS])

Then you would create a function in a Module that would run through all the possiblities like this:

Function getPassFail(rcode, rcomments)
Dim ret As String 
ret = ""
' by default it will be blank
If (repcode Like "*U*") Then ret = "FAILURE"
If (repcode Like "*R*") And (IsNull(rcomments) = False) Then ret = "FAILURE" 
' checks for failure
If (repcode LIKE "*N*") then ret="PASS"
If (repcode LIKE "*R*") AND (isnull(rcomments)) then ret="PASS"
If (repcode NOT LIKE "*N*") AND (repcode NOT LIKE "*R*") AND (repcode NOT LIKE "*U*") then ret="PASS" 
' checks for passing
getPassFail = ret 
' returns result
End Function

There are a few problems with my code:

1. It will always return "PASS" because of the 'N' issue I mentioned prior.
2. Its possible for a record to first fail, and then pass. If a record has a 'U' and an 'N' in it, it will first fail because of the 'U' and then pass because of the 'N'

So if you could explain the precedence of the criteria (which to do first, second, third, etc) I could correct my code.

-----------------------------------------------------------
Thanks Plog, I have never tried this in VB but it seems a lot easier to write.
Sequential Order:
FAILURES
1. Check for failure records in fields [Rep_Code] + [Repair Comments]
1. [Rep_Code]="*u*"
2. [Rep_Code]="*r*" And [Repair_Comments]=Not IsNull([Repair_Comments])
PASSING RECORDS
2. Check for passing records in fields [Rep_Code] + [Repair Comments]
1. [Rep_Code]="*n*"
2. [Rep_Code]="*r*" And [Repair_Comments]=IsNull([Repair_Comments])
I took the third criteria out to eliminate the all pass issue...<>u or <>n or <> r

In your VB code, the failure should be Not Isnull (If (repcode Like "*R*") And (IsNull(rcomments) = False) Then ret = "FAILURE")

Is this the correct code mod below?

Function getPassFail(rcode, rcomments)
Dim ret As String
ret = ""
' by default it will be blank
If (repcode Like "*U*") Then ret = "FAILURE"
If (repcode Like "*R*") And Not (IsNull(rcomments) = False) Then ret = "FAILURE"
' checks for failure
If (repcode Like "*N*") Then ret = "PASS"
If (repcode Like "*R*") And (IsNull(rcomments)) Then ret = "PASS"
' checks for passing
getPassFail = ret
' returns result
End Function

 

[plog]

You're code looks good, but it's still possible for a record to first fail, and then pass. If a record has a 'U' and an 'N' in it, it will first fail because of the 'U' and then pass because of the 'N'. Is that fine or would you prefer a different result?

Also, its possible to return nothing. By default the value is "", if it never meets a set of criteria it passes it will remain "". Is that fine?

Another way to go about this is to set the default to PASS (ret="PASS" instead of ret="") and then only check the failure criteria. Or vice versa, that way it forces the result to be PASS or FAILURE.

 

[mark6109]

You're code looks good, but it's still possible for a record to first fail, and then pass. If a record has a 'U' and an 'N' in it, it will first fail because of the 'U' and then pass because of the 'N'. Is that fine or would you prefer a different result?

Also, its possible to return nothing. By default the value is "", if it never meets a set of criteria it passes it will remain "". Is that fine?

Another way to go about this is to set the default to PASS (ret="PASS" instead of ret="") and then only check the failure criteria. Or vice versa, that way it forces the result to be PASS or FAILURE.

I put the "" return result because the circuit board might still be in the repair process and would not require a result if it has not completed troubleshooting testing. Also, The repair code would never have a combination of U and a N in the same field, it could only be either or. Thanks for all your help, I got it!!