Circular Logic? IIF statement 1

[sxschech]

Have a field made up of six digits, fields are broken down into 3 pairs of two digits. These are then joined to a lookup table that has a two digit code and a description.

The issue is final output is to be one field showing only description.

I suppose I could do it numerically by looking at all the numbers, but since the description can be evaluated based on the first character, it would seem less typing.

Logic is if the first character of the description = "U" then look at the description that is equivalent to the 2nd pair of numbers, and if that is also evaluating to "U", look at the 3rd pair's description. If the last pair is also "U", then display the "U" description of the first pair.

994170

99=UNLA
41=UNDC
70=SPAS

Therefore, the query should display SPAS

I first tried this and didn't get the results I needed:
IIf(Left([Maj1].[EMTCode],1)="U",[Maj2].[EMTCode],
IIf(Left([Maj2].[EMTCode],1)="U",[Maj3].[EMTCode],
[Maj1].[EMTCode]))

I next tried this on the field grid:
IIf(Left([Maj1].[EMTCode],1)="U",
IIf(Left([Maj2].[EMTCode],1)="U",
IIf(Left([Maj3].[EMTCode],1)="U",[Maj1].[EMTCode],
IIf(Left([Maj2].[EMTCode],1)="U",
IIf(Left([Maj3].[EMTCode],1)="U",[Maj2].[EMTCode],
[Maj3].[EMTCode])))))


I probably need more iifs? as it currently is showing a lot of blanks rather than putting in a description. The first version doesn't have blanks, all fields are filled in.

Thanks.

 

[MajP]

one nested iif is bad. Two nested iifs are really bad. And beyond that is just dumb. Build your own function

Public Function getCode(emt1 As Variant, emt2 As Variant, emt3 As Variant) As String
  emt1 = Nz(emt1, "")
  emt2 = Nz(emt2, "")
  emt3 = Nz(emt3, "")
  If Not UCase(Left(emt1, 1)) = "U" Then
    getCode = emt1
  ElseIf Not UCase(Left(emt2, 1)) = "U" Then
    getCode = emt2
  ElseIf Not UCase(Left(emt3, 1)) = "U" Then
    getCode = emt3
  Else
    getCode = ""
  End If
End Function

What happens if the 1st and 3rd start with an U?
What happens with null values?
I assume you want to return the first value that does not start with a U, but I did not know what to do with nulls.

 

[PHV]

IIf(Left([Maj1].[EMTCode],1)="U",
IIf(Left([Maj2].[EMTCode],1)="U",
IIf(Left([Maj3].[EMTCode],1)="U",
[Maj1].[EMTCode],
[Maj3].[EMTCode]),
[Maj2].[EMTCode]),
[Maj1].[EMTCode])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I would question the table structures of any solution that seems to have repeating similar tables with names like Maj1, Maj2, and Maj3.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, I guess that Maj[123] are aliases ...

 

[sxschech]

Thanks PHV that worked perfectly.

Yes the Maj[123] are aliases. I joined the table 3 times for each portion of the major code which I had split out into 3 fields. Perhaps there is a more efficient way to do this without splitting the field in one query and then creating a second query.

MajP. Sorry about the extra iifs. That's what happens for trying to do something late on a Friday afternoon. Not thinking clearly, but since PHV's solution worked, I'll go with that. In the case of this data set, my understanding is that the field will not be null. It would be "000000".

 

[sxschech]

When I have time, I'll look at MajP's code and see if I can modify it to look at the whole 6 digit field so I can use one query instead of two.