Question regarding Query on multiple fields

[3Mark3]

Hello all, well since you all were soo quick with helping me last time and I'm rather stuck right now, I'm hoping someone may have a solution to what I'm trying to do.

The fields and corrosponding data looks like this in the query (query name is bucket_results):

Account Number
Transaction Type Code
Bal unapplied code 1
Bal Unapplied code 2
Bal Unapplied code 3
Bal Unapplied code 4
Bal Unapplied code 5
field_result

What I'm trying to do is this. There is a code in the Transaction Type Code. There are about 18 different codes. Each Account may or may not have all the codes, but may have a few. The codes look like below, and if there is a code for that account, it will be represented by the 3rd character. See below:

UF* = *(in the bal Unapplied code fields)
UF0 = 0(in the bal Unapplied code fields)
UF4 = 4(in the bal Unapplied code fields)
UF5 = 5(in the bal Unapplied code fields)

And so forth

the query I'm trying to build, will look in each of the "Bal unapplied code" fields (1-5). I need the query to check to see if each code for each record is there (for instance, account 0000556677 with a code of UFF to see if there is a F in each of those fields). If there isn't a "F" in any of the 5 Bal unapplied fields, I need to return the results in the "field_result" to something like "No Code"

I hope I'm being clear about what I'm talking about. I know it's going to be SQL driven, I'm just not sure how to do this. Can someone provide me some kind of a solution? Thank you for taking the time in advance!

 

[3Mark3]

I have been able to write the code to check for the first Bal Unapplied code 1...but I don't knwo how to create the code to check all 5 fields before returning back the results that it couldnt' find it. This is the code as of right now will only check the first "Bal Unapplied Code1":

IIf("UF"+[unapplied balance_update].[bal Unapplied Code 1]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found","Not Found")

 

[3Mark3]

Anyone know if this could be the right code? I can't tell if the And or OR is more appropriate...I can't get it to return the correct results:

IIf("UF"+[unapplied balance_update].[bal Unapplied Code 1]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result] And IIf("UF"+[unapplied balance_update].[bal Unapplied Code 2]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result] And IIf("UF"+[unapplied balance_update].[bal Unapplied Code 3]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result] And IIf("UF"+[unapplied balance_update].[bal Unapplied Code 4]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result] And IIf("UF"+[unapplied balance_update].[bal Unapplied Code 5]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found","Not Found")))))