data comparison query help

[aselm01]

I need to compare multiple values against a set of criteria and return a Pass or Fail. I need to do this whether I have just 1, 2, 3 or all 4 values. If any 1 value being compared does not meet the criteria then all being compared fail. The only Pass condition will be if all values being compared are good.



I thought I could do it like I have below in a query, but I am running into a problem with Access. It keeps putting a [ ] around the fail in the case of a comparison between two and around the Pass, Fail, Fail, Fail for the multiple comparison.

Result_Value_1: IIf([Value_A] Between 0.22 And 0.3, IIF([Value_B] Between 0.22 And 0.3,"Pass","Fail"),“[Fail]”)

Result_Value_2: IIf([Value_A] Between 0.22 And 0.3, IIF([Value_B] Between 0.22 And 0.3, IIF([Value_C] Between 0.22 And 0.3,”[Pass]”,"[Fail]"),“[Fail]”),”[Fail]”)

This is a picture from the query that shows Access putting the brackets in.



So when you run the query it asks for a value for each. I just click OK without putting anything in and it works, I think. So it must be something simple that I am missing or don’t know about.

I am new to Access and don’t know much about VBA or anything like that, but I am willing to try.

[June7]

I am surprised Access just doesn't choke on the expressions because they are missing paren(s) at the end but apparently Access 2013 is trying to 'fix' the error by assuming you want a field as the alternate value for the outer IIf.

Keep this in mind: for every IIf in the nesting, need a paren at the end.

Clarify the rule: If any one value is not between the given range, then Fail, otherwise Pass?

Consider:
IIf([Value_A] Between 0.22 And 0.3 AND [Value_B] Between 0.22 And 0.3 AND [Value_C] Between 0.22 And 0.3 AND [Value_D] Between 0.22 And 0.3,"Pass","Fail")

[aselm01]

I am surprised Access just doesn't choke on the expressions because they are missing paren(s) at the end but apparently Access 2013 is trying to 'fix' the error by assuming you want a field as the alternate value for the outer IIf.

Keep this in mind: for every IIf in the nesting, need a paren at the end.

Hi June7!

What missing parenthesis? They are all there as far as I can tell. The example below has 2 opening and 2 closing.

Result Value1: IIf([Dither_Pin_30_U3] Between 0.22 And 0.3,IIf([Dither_Pin_30_U6] Between 0.22 And 0.3,"Pass","Fail"),[“Fail”])

Please provide an example if you can, but the real question is am I doing it right or not?

[June7]

I hope you didn't read the bad post I deleted and replaced with this one.

Sorry, here is example of what I had in mind.

FSV = IIf(dblFine02 < 4, "NFS", IIf(dblFine02 < 10, "F1", IIf(dblFine02 < 21, "F2", "F3")))

On second look, yours should work but did you consider the alternate expression I suggested?

[aselm01]

I hope you didn't read the bad post I deleted and replaced with this one.

Sorry, here is example of what I had in mind.

FSV = IIf(dblFine02 < 4, "NFS", IIf(dblFine02 < 10, "F1", IIf(dblFine02 < 21, "F2", "F3")))

On second look, yours should work but did you consider the alternate expression I suggested?

I tried your first suggestion and it works if you have all 4 vaules, but not if any one is blank.

I don't quite understand this suggestion though...

I get that you are evaluating dblFine02, but don't understand the "NFS" part and what is the F1, F2, F3 part? Are they the fields I would want to compare?

[June7]

That's a line directly out of my code. Didn't really intend you to try and adapt, just showing where my orientation came from. No, those are literal values.

If there is no value (meaning null), the result should be Fail?

You could try Nz:

If(Nz([Value_A],0) Between 0.22 And 0.3 AND Nz([Value_B],0) Between 0.22 And 0.3 AND Nz([Value_C],0) Between 0.22 And 0.3 AND Nz([Value_D],0) Between 0.22 And 0.3,"Pass","Fail")

[aselm01]

That's a line directly out of my code. Didn't really intend you to try and adapt, just showing where my orientation came from. No, those are literal values.

If there is no value (meaning null), the result should be Fail?

You could try Nz:

If(Nz([Value_A],0) Between 0.22 And 0.3 AND Nz([Value_B],0) Between 0.22 And 0.3 AND Nz([Value_C],0) Between 0.22 And 0.3 AND Nz([Value_D],0) Between 0.22 And 0.3,"Pass","Fail")

June7,

If a value is null or blank, but you have other values to compare and they all meet the criteria then it should return a Pass. The spreadsheet I am trying to copy in access has 4 fields that are all compared against the same criteria, but you don't always have 4 values. You could have just 1, 2, 3, or all 4. So if I have only 1 then it needs to be evaluated and a pass or fail returned. If I have 2 values then they both have to be evaluated and then if they both pass then the return would be a Pass. If not then the return would be a Fail and so on.

I tried this and it returned a Fail when values were blank.

[June7]

Seems Null would be an issue in your original expression as well.

If all values are null, should return null?

Calculate four fields, here is the first one:

F1: IIf(IsNull([Value_A]), Null, IIf([Value_A] Between 0.22 And 0.3, "P", "F"))

Calculate a fifth field:

PF: IIf(IsNull(F1 & F2 & F3 & F4), Null, IIf(Not F1 & F2 & F3 & F4 Like "*F*", "Pass", "Fail"))

Aside from a custom VBA function, that's the only way I can see to handle null.

[aselm01]

Seems Null would be an issue in your original expression as well.

If all values are null, should return null?

Calculate four fields, here is the first one:

F1: IIf(IsNull([Value_A]), Null, IIf([Value_A] Between 0.22 And 0.3, "P", "F"))

Calculate a fifth field:

PF: IIf(IsNull(F1 & F2 & F3 & F4), Null, IIf(Not F1 & F2 & F3 & F4 Like "*F*", "Pass", "Fail"))

Aside from a custom VBA function, that's the only way I can see to handle null.

So would I just be substituting my field names in for F1, F2, etc? What about "*F*"? What about the brackets around the values? Are they not needed here?

If you can suggest the VBA code I am game. This seems a bit more complicated than what Access will let you do. Or if it will let you do it it seems very drawn out and painful.

[June7]

If you use something other than F1, F2, F3, F4 to name the constructed fields, yes, use those. Brackets around the field names, I just forget to type them. The "*F*" exactly as shown, it's the conditional criteria.

[aselm01]

I have not had time to try this yet. I will give it a shot maybe this weekend and post my findings. Thanks for all of your help so far. You have been a great help in this project.