Run out of iifs 1

[honeypot3]

Hi there

I have a feeling I am going about this in the wrong way but do not know another approach that would work. I need to be able to compare one field's records (of which there are 19 possibilities) to return a value from a field with the same name. The nested iif function was working fine (as below) until I ran out of iifs and Access complained the query was too complex (with which I agree) but I am not sure of what other approach I can take. SQL so far which works fine looks like this: (please forgive as SQL is very new to me!)

SELECT [Labour Available monthly values].Resource, IIf([resource]="site managers",[site managers],IIf([resource]="ass sm",[ass sm],IIf([resource]="RLOs",[RLOs],IIf([resource]="Joiners",[Joiners],IIf([resource]="App Joiners",[Apprentice Joiners],IIf([resource]="Brick Layers",[Brick Layers],IIf([resource]="Labourers",[labourers],IIf([resource]="Drivers",[Drivers],IIf([resource]="Storeman",[Storeman],IIf([resource]="Plasterers",[Plasterers],IIf([resource]="Plaster patchers",[Plaster patchers],IIf([resource]="Wall Tilers",[Wall Tilers],IIf([resource]="Decorators",[Decorators],IIf([resource]="Floorers",[Floorers],0)))))))))))))) AS test, [Base figures for actual monthly updates].[Contract number]
FROM [Base figures for actual monthly updates] INNER JOIN [Labour Available monthly values] ON [Base figures for actual monthly updates].[Contract number] = [Labour Available monthly values].[Contract number];


But I need to add another 5 levels - any ideas or a possible different approach?

 

[PHV]

You may consider an union query:
SELECT L.Resource, [site managers] AS test, B.[Contract number]
FROM [Base figures for actual monthly updates] B INNER JOIN [Labour Available monthly values] L ON B.[Contract number] = L.[Contract number]
WHERE L.resource = "site managers"
UNION SELECT L.Resource, [ass sm], B.[Contract number]
FROM [Base figures for actual monthly updates] B INNER JOIN [Labour Available monthly values] L ON B.[Contract number] = L.[Contract number]
WHERE L.resource = "ass sm"
...
UNION SELECT L.Resource, [Floorers], B.[Contract number]
FROM [Base figures for actual monthly updates] B INNER JOIN [Labour Available monthly values] L ON B.[Contract number] = L.[Contract number]
WHERE L.resource = "Floorers"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[honeypot3]

Hi PH

This works fab - thanks you are a star!!

 

[MichaelRed]

I generally prefer to break out complex selections into a module / code.

Since SQL is essientially just a string, it can only be made to look like a structure by manually placing it in an "Editor", and adding the structure (indents and outdents), I see no particular benefit in a program like Ms. A. to forcing all that logic (and counting of Left / right parens) into the query builder.

While cretainly NOT tested, I did take a semblance of your torture test and reanslate to code:

Public Sub basNestedIIIF2SelectCase(strResource)

    Dim strSQL As String
    Dim Quo As String * 1

    Quo = Chr(34)

    Select Case strResource
    
        Case Is = "ass sm "
            basNestedIIF2SElectCase = "[ass sm]"

        Case Is = "RLOs "
            basNestedIIF2SElectCase = ""

        Case Is = "App Joiners "                            '   ***** This one is the Bugga bear ***
            basNestedIIF2SElectCase = "[Apprentice Joiners]"

        Case Is = "Brick Layers "
            basNestedIIF2SElectCase = ""

        Case Is = "Labourers "
            basNestedIIF2SElectCase = ""

        Case Is = "Drivers "
            basNestedIIF2SElectCase = ""

        Case Is = "Storeman "
            basNestedIIF2SElectCase = ""

        Case Is = "Plasterers "
            basNestedIIF2SElectCase = ""

        Case Is = "Plaster patchers "
            basNestedIIF2SElectCase = ""

        Case Is = "Wall Tilers "
            basNestedIIF2SElectCase = ""

        Case Is = "Decorators "
            basNestedIIF2SElectCase = ""

        Case Is = "Floorers "
            basNestedIIF2SElectCase = "[Floorers]"
    End Select

    'OR     '*******************************************************************************************

    Select Case strResource

        Case Is = "App Joiners "                            '   ***** This one is the Bugga bear ***
            basNestedIIF2SElectCase = "[Apprentice Joiners]"

        Case Else
            basNestedIIF2SElectCase = "[" & sreResource & "]"
    End Select

    'EOR    '*******************************************************************************************

End Sub

Please CAREFULLY look at the second / lower select case statement, which becomes an obvious soloution as soon as you begin to set up the first. Further note that - - - WITH ONE SIMPLE FIELD NAME CHANGE - - - no structure or complexity is necessary at all!

Here, we appear to have an exercise in poor design / planning, more than a need to expand the programatic capability (of Ms. A, VBA, or any of the TOOLS), we need to look at the issue and simply conclude that more thought and less frentic activity sould be the preferred results.



MichaelRed