IIF statement producing error message.

[serino]

I have a problem with this query. Whe I added the third IIF statement for SubPreFix "3090" I received the following error

[red] "The expression you entered exceeds the 1,024 character for the query design build" [/red]

I have more IIF statements I need to add. Is there a way to add these additional IIF statements?

[blue]
SELECT JP105HistoryStreamLined.JobNumber, JP105HistoryStreamLined.SubPreFix, JP105HistoryStreamLined.CTCDTE, JP105HistoryStreamLined.LastOfInvDate, JP105HistoryStreamLined.NvNoTax, JP105HistoryStreamLined.DSCRPT, IIf([JP105HistoryStreamLined]![SubPreFix]=3007 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([JP105HistoryStreamLined]![SubPreFix]=3007 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([JP105HistoryStreamLined]![SubPreFix]=3007 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 3*"),"EC3",IIf([JP105HistoryStreamLined]![SubPreFix]=3008 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([JP105HistoryStreamLined]![SubPreFix]=3008 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([JP105HistoryStreamLined]![SubPreFix]=3008 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 3*"),"EC3",IIf([JP105HistoryStreamLined]![SubPreFix]=3090 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([JP105HistoryStreamLined]![SubPreFix]=3090 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([JP105HistoryStreamLined]![SubPreFix]=3090 And ([JP105HistoryStreamLined]![DSCRPT] Like "*Lot Clean 3*"),"EC3",[JP105HistoryStreamLined]![PhaseNME]))))))))) AS NEWDSCRP, JP105HistoryStreamLined.PhaseNME, JP105HistoryStreamLined.PhaseNumber
FROM JP105HistoryStreamLined;[/blue]

Any help appreciated!

 

[SkipVought]

Hi,

Try this....

SELECT
  JobNumber
, SubPreFix
, CTCDTE
, LastOfInvDate
, NvNoTax
, DSCRPT
, IIf([SubPreFix]=3007 And ([DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([SubPreFix]=3007 And ([DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([SubPreFix]=3007 And ([DSCRPT] Like "*Lot Clean 3*"),"EC3",IIf([SubPreFix]=3008 And ([DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([SubPreFix]=3008 And ([DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([SubPreFix]=3008 And ([DSCRPT] Like "*Lot Clean 3*"),"EC3",IIf([SubPreFix]=3090 And ([DSCRPT] Like "*Lot Clean 1*"),"EC1",IIf([SubPreFix]=3090 And ([DSCRPT] Like "*Lot Clean 2*"),"EC2",IIf([SubPreFix]=3090 And ([DSCRPT] Like "*Lot Clean 3*"),"EC3",[PhaseNME]))))))))) AS NEWDSCRP
, PhaseNME
, PhaseNumber

FROM JP105HistoryStreamLined;

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

You may simplify a lot:
SELECT JobNumber, SubPreFix, CTCDTE, LastOfInvDate, NvNoTax, DSCRPT
,IIf([SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 1*","EC1"
,IIf([SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 2*","EC2"
,IIf([SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 3*","EC3"
,[PhaseNME]))) AS NEWDSCRP, PhaseNME, PhaseNumber
FROM JP105HistoryStreamLined;

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

 

[serino]

Thank You both! I will give this try.

 

[KyleS]

Also, you could think about using a Switch statement,

SELECT JobNumber, SubPreFix, CTCDTE, LastOfInvDate, NvNoTax, DSCRPT,
Switch([SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 1*","EC1", [SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 2*", "EC2", [SubPreFix] In (3007,3008,3090) And [DSCRPT] Like "*Lot Clean 3*", "EC3", TRUE, [PhaseNME])
AS NEWDSCRP, PhaseNME, PhaseNumber
FROM JP105HistoryStreamLined;

It will get rid of 4 or 5 characters for each variation you're testing. (you can lose the "IIF (" and the closing bracket)

Kyle

 

[dhookom]

IMHO you should never be maintaining an expression like this in a query. This is data relationships and should be maintained in tables when possible. My second option would be to create a small user-defined function in a module.

My last option would be maintaining a complex expression that probably will need updating when there are new subprefix values and/or "Lot Clean" values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JA3395]

This might be easier to maintain...

SELECT JP105HistoryStreamLined.JobNumber,
JP105HistoryStreamLined.SubPreFix,
JP105HistoryStreamLined.CTCDTE,
JP105HistoryStreamLined.LastOfInvDate,
JP105HistoryStreamLined.NvNoTax,
JP105HistoryStreamLined.DSCRPT,
GetNEWDSCRP(JP105HistoryStreamLined]![SubPreFix], [JP105HistoryStreamLined]![DSCRPT],[JP105HistoryStreamLined]![PhaseNME]) as NEWDSCRP,
JP105HistoryStreamLined.PhaseNME,
JP105HistoryStreamLined.PhaseNumber
FROM JP105HistoryStreamLined;

Function GetNEWDSCRP(ByVal SubPreFix As Variant, ByVal Dscrpt As Variant, ByVal PhaseNME As Variant) As String

    Select Case SubPreFix
    Case "3007"
        If InStr(1, Dscrpt, "Lot Clean 1", vbTextCompare) > 0 Then
            GetNEWDSCRP = "EC1"
        Else
            If InStr(1, Dscrpt, "Lot Clean 2", vbTextCompare) > 0 Then
                GetNEWDSCRP = "EC2"
            Else
                If InStr(1, Dscrpt, "Lot Clean 3", vbTextCompare) > 0 Then
                    GetNEWDSCRP = "EC3"
                End If
            End If
        End If
    Case "3008"
        If InStr(1, Dscrpt, "Lot Clean 1", vbTextCompare) > 0 Then
            GetNEWDSCRP = "EC1"
        Else
            If InStr(1, Dscrpt, "Lot Clean 2", vbTextCompare) > 0 Then
                GetNEWDSCRP = "EC2"
            Else
                If InStr(1, Dscrpt, "Lot Clean 3", vbTextCompare) > 0 Then
                    GetNEWDSCRP = "EC3"
                End If
            End If
        End If
    Case "3090"
        If InStr(1, Dscrpt, "Lot Clean 1", vbTextCompare) > 0 Then
            GetNEWDSCRP = "EC1"
        Else
            If InStr(1, Dscrpt, "Lot Clean 2", vbTextCompare) > 0 Then
                GetNEWDSCRP = "EC2"
            Else
                If InStr(1, Dscrpt, "Lot Clean 3", vbTextCompare) > 0 Then
                    GetNEWDSCRP = "EC3"
                End If
            End If
        End If
    Case Else
        GetNEWDSCRP = PhaseNME
    End Select

End Function

J.