I use the Iif statement in a query and it works well. However I have need to increase the number of Iif's. The query states that there are too many and it will not work.
Should I change to a Select Case statement and if so How can I make it work. I need to use it in the query.
Can I make a function and the Call it into the query. If so, can you send me in the correct direction to learn how to make the function and also the Code to use.

Below is the Iif statement that I use in the query, it works now, but I need to add about another 20 Iif's. I have made line breaks to make it easier to read.

Track_Front_Normall: IIf([Design_Type] = "Outlook",[Track_Length]-103,
IIf([Design_Type] = "Infinity" And [Door_Type] = "Single", [Track_Length]-75,
IIf([Design_Type] = "Infinity" And [Door_Type] = "Pulldown", [Track_Length]-70,
IIf([Design_Type] = "Retractable" And [Normal_Track] = "0", 0,
IIf([Design_Type]="Retractable" And [Door_Type] = "Single", [Track_Length],
IIf([Design_Type] = "Retractable" And [Door_Type] = "Double", [Track_Length],
IIf([Design_Type] = "Retractable" And [Door_Type] = "Pulldown", [Track_Length],
IIf([Design_Type] = "Infinity" And [Door_Type] = "Double" And [Door_Offset_Left] > = 0.05, 0,
IIf([Design_Type] = "Infinity" And [Door_Type] = "Double", ([Track_Length]-144)/2,
IIf([Design_Type] = " Infinity_Zipline" And [Door_Type] = "Double" And [Door_Offset_Left] > = 0.05, -72,
IIf([Design_Type] = " Infinity_Zipline" And [Door_Type] = "Double", ([Track_Length]-144)/2,
IIf([Design_Type] = " Infinity_Zipline" And [Door_Type] = "Pulldown", ([Track_Length]-70 ,100)))))))))
100))))))))))))
Any help appreciated
Regards
Charlie 

Even if there are workarounds for your question, like logic concatenation for example, you will be always limited by the amount of text you can include in expressions in a query.
In my tests with Access 2010 x64 Access one can have up to 13 nested IIFs 14 expressions with Switch, and unlimited concatenations using logic (not just functions).
However, is a lot easier to go to VBA and use simple Select Case statements to do the same.
Regards,
Diego

Thanks Diego,
But how Do I make a Case statement. I have tried, but I cannot get it to look up the fields to check their value.
Can you suggest a link to where I can get some instructions
Regards
Charlie

Its pretty simple really

Take a look http://msdn.microsoft.com/en-us/library/2h...v=vs.80%29.aspx
In your case I'd probably try Select Case True, because you have several fields with logical operators, something like:

The use of Me! or rst! will depend on how you are going to get the values of those fields, from a form or from a recordset...

This post has been edited by dipetete: Apr 28 2012, 12:00 AM

Thanks for that,
I have got it going but only on checking 1 field.
I would like to check on three Fields but don't know how to reference them.
Design_Type AND Door_Type are both Strings and Door_Offset_Left is an Integer

Case is Design_Type = "Outlook" and Door_Type = "Single" AND Door_Offset_Left >1
100



My Code so far that works is:
Function This_Length(Design_Type As String) As String
Dim Charlie As Integer

Select Case Design_Type
Case Is "Retractable"
This_Length = 99
Case Is = "Outlook"
This_Length = 88
Case Is = "Infinity"
This_Length = 77
Case Else
This_Length = 66
End Select

Any help much appreciated
Regards
Charlie

You can increase the flexibilty of your database and remove the need for code if you create a Table object (i'll call it tblDesignTypes) with a structure that looks like this:

tblDesignTypes
-------------------------
Design_Type (Text, Primary Key)
Length (Number/Long Integer)


Then create a relationship with referential integrity (Cascade Updates) from tblDesignTypes table to the tables that use the Design_Type data.

Then ... in your Query, you can bring in tblDesignTypes and JOIN the table to the other tables in your Query object as appropriate. By doing that, you now have access to the Length field of tblDesignTypes. With that access, you can directly reference the field in your expressions.

So ... the SQL View of your Query object might look like this:

SELECT tblMain.Field1, tblMain.Design_Type, [Track_Length]-tblDesignTypes.Length As Track_Front_Normal
FROM tblMain LEFT JOIN tblDesignTypes ON tblMain.Design_Type = tblDesignTypes.Design_Type


Now, when you have a new Design_Type, just add it to the table tblDesignTypes. On on your Forms, change the text box you used for Design_Type in your main table to a Combo Box, then use the tblDesignTypes table as the source of your rows in the Combo Box. By doing this, you allow your users to select only valid Design_Types, plus your Query object will then return all the correct Track_Front_Normal values.

Most importantantly, this is how databases become your friend --- by designing the tables well, you can avoid a lot of work arounds that are not neccessary, or desired.

Nice Brent! (IMG:style_emoticons/default/thumbup.gif)

Thanks! I hope it helps the topic starter! (IMG:style_emoticons/default/thumbup.gif)