Translate IIf-statement to table

[TomBP]

Hi,

I have a lot of long IIf-statements in my database and apparently I just went overboard. When trying to add a criteria an error pops up: "Expression too complex in query expression".

That's why I think it's time to convert some IIf-statements to a table logic. I'm however struggling to piecing together the puzzle. E.g. I'm not able to translate the [value_if_false] argument to a table. Furthermore the multiple fields ([Created by] and [POrg] aren't helping either.

Could someone help me out by translating the IIf-statement below to a table?

Auto/Man: IIf([Created by]="V0XSLLS";"auto";IIf([Created by]="V1BAT02";"auto";IIf([Created by]="V0XBATCH";"auto";IIf([Created by]="V0XPM";"auto";IIf([Created by]="BTMMABAT";"auto";IIf([Created by]="V0XDISP";"auto";IIf([Created by]="BTPSBAT";"auto";IIf([Created by]="SHSBATCH";"auto";IIf([Created by]="V0XSLA";"auto";IIf([POrg]="BE35";"auto";IIf([Created by] Like "O3*";"auto";"man")))))))))))

 

[boblarson]

What do you mean by translate to a table?

Do you mean to put in a table the values and then be able to return the appropriate value based on the table? If so, what I would do is to first create a table with two fields

ItemName
ItemValue

and under ItemName you would put V0XSLLS, VOXSLA, etc. and for their values auto or man for each.

Then create a function in a standard module (remember to not name the module the same as the procedure):

Function GetAutoMan(strInput As String) As String
Dim rst As DAO.Recordset
Dim strSQL As String
 
strSQL = "SELECT * FROM TABLENAMEHERE WHERE ItemName=" & strInput
 
Set rst = CurrentDb.OpenRecordset(strSQL, , dbOpenForwardOnly)
 
If rst.RecordCount > 0 Then
   GetAutoMan = rst!ItemValue
Else
   GetAutoMan = "Not Found"
End If
rst.Close
Set rst = Nothing
End Function

Then in your query just call it like:

Auto/Man:GetAutoMan([Created by])

Now, you may wonder why I used a recordset. It actually supposedly (from Allen Browne) works faster than the DLookups.

 

[MSAccessRookie]

Hi,

I have a lot of long IIf-statements in my database and apparently I just went overboard. When trying to add a criteria an error pops up: "Expression too complex in query expression".

That's why I think it's time to convert some IIf-statements to a table logic. I'm however struggling to piecing together the puzzle. E.g. I'm not able to translate the [value_if_false] argument to a table. Furthermore the multiple fields ([Created by] and [POrg] aren't helping either.

Could someone help me out by translating the IIf-statement below to a table?

Considering the fact that most of your IIf() conditions (except for the last two) are all ([Created By] = {Something}), perhaps you could group those into an IN Statement, and combine the results with the LIKE Statement, using the final statement as the False Result. Something like the following is what I was thinking of.

IIf(([Created by] IN ("V0XSLLS", "V1BAT02", "V0XBATCH", "V0XPM", "BTMMABAT", "V0XDISP", "BTPSBAT", "SHSBATCH", "V0XSLA", "BE35") OR ([Created by] Like "O3*")), "auto", "man")

-- Rookie

**** I just noticed that Bob Larson had responded with a Function to deal with the problem. Once again I am a little too slow on the response.

I am a great proponent of VB Code when it can be used, but I am not sure how the Function would handle the Final condition in the set of conditions, which is a LIKE comparison.

 

[boblarson]

MSAccessRookie's solution should work for your immediate problem. However, if you have the possibility of things being added, then the table method makes sense as you can just add however many and it won't require any rewriting of the queries.

 

[TomBP]

MSAccessRookie,

Thanks for your reply. I was unaware of the existence of an IN-statement. This indeed reduces the complexity.

As you pointed out however and which is backed up by boblarson perhaps it's better to make us of the table method.


boblarson,

Do you mean to put in a table the values and then be able to return the appropriate value based on the table?

That's exactly what I mean. I however don't know whether it is possible to build the same logic (see below) of the IIf-statement into a table.

Required logic

• Like*
• Value if false = man
• Multiple fields: [Created by] and [POrg]