Expression too complex

[bandidom9]

I need to do this query. Is there a way to make it simpler so wont get the complez expression message?

SELECT UIC, CCtr,

IIf (CCtr LIKE "230A?00*" OR CCtr LIKE "230A?N09*" OR CCtr LIKE "230A?N03*", "N00/09/03",
IIf (CCtr LIKE "230A?N0AL*", "N00AL",
IIf (CCtr LIKE "230A?N04%", "N04",
IIf (CCtr LIKE "230A?N05*", "N05",
IIf (CCtr LIKE "230A?N1*", "N1",
IIf (CCtr LIKE "230A?NTEN0*", "N10",
IIf (CCtr LIKE "230A?N3*" OR CCtr LIKE "230A?N4*", "N3/4",
IIf (CCtr LIKE "230A?N5*", "N5",
IIf (CCtr LIKE "230A?N6*", "N6",
IIf (CCtr LIKE "230A?N7*", "N7",
IIf (CCtr LIKE "230A?N8*", "N8",


IIf (CCtr LIKE "230A?N91*", "N91",
IIf (CCtr LIKE "230A?N0CC*", "NOCC",
IIf (CCtr LIKE "230A?N0GC*", "NOGC",
IIf (CCtr LIKE "230A?OP*", "NOP")))))))))))))) AS DivCode

FROM [Quarterly Report]
WHERE ( UIC LIKE "*23" OR UIC = "3598A");

[orange]

Can you tell us in plain English what you are trying to do?

My first impression is that you have a custom code (identifier or whatever) that includes at least 2 concepts, When you want to use that custom code, you have to parse it to separate the fields/concepts.

Seems like a design issue.

[bandidom9]

Plain english is not my first language........ More than a database im usin acces as a report creator. I receive a excel sheet with data that have the CCtr fiel on it. that fiel contains an ID from wich a division can be identified. I want this query to create the same sheet with an new field displaying the division code. If i erase one line from the code the code works perfectly. It seems that i just went over some sort of limit. by just one line.

[orange]

OK, so it seems you do have some sort of processed data in the text file and you are bringing it into Access. Then separating out some values to create DivCodes.

You are basically doing a Lookup on these incoming CCtr field values.

If the CCtr values are known, you could build a table in Access - let's call it tblDivCodesToDisplay.

It would have fields
IncomingCCtr text
OutputDivCode text

You could populate that table with queries based on the values you have in the many IIF statements.

Then you could revise the query to include the new table -- something along these lines --
---untested----air code ----
SELECT UIC, CCtr, outputDivCode
FROM [Quarterly Report] inner join tblDivCodesToDisplay
ON [Quarterly Report].CCtr = tblDivCodesToDisplay.IncomingCCtr
WHERE
UIC LIKE "*23" OR UIC = "3598A"

[bandidom9]

The values are known but i have to be able to use wildcards. What a did to overcome the nexted if limit is I created a Funtion and it worked! Thanks for the help i really appreciated.

Public Function Code(CC As String)
If (CC Like "230A?00*" Or CC Like "230A?N09*" Or CC Like "230A?N03*") Then
Code = "N00/09/03"
ElseIf (CC Like "230A?N0AL*") Then
Code = "N00AL"
ElseIf (CC Like "230A?N04*") Then
Code = "N04"
ElseIf (CC Like "230A?N05*") Then
Code = "N05"
ElseIf (CC Like "230A?N1*") Then
Code = "N1"
ElseIf (CC Like "230A?NTEN0*") Then
Code = "N10"
ElseIf (CC Like "230A?N3**" Or CC Like "230A?N4*") Then
Code = "N3/4"
ElseIf (CC Like "230A?N5*") Then
Code = "N5"
ElseIf (CC Like "230A?N6*") Then
Code = "N6"
ElseIf (CC Like "230A?N7*") Then
Code = "N7"
ElseIf (CC Like "230A?N8*") Then
Code = "N8"
ElseIf (CC Like "230A?N91*") Then
Code = "N91"
ElseIf (CC Like "230A?N0CC*") Then
Code = "NOCC"
ElseIf (CC Like "230A?N0GC*") Then
Code = "NOGC"
ElseIf (CC Like "230A?OP*") Then
Code = "NOP"
End If
End Function

[orange]

Glad you have it resolved.