1. Registered User
Join Date
Jan 2013
Posts
53

Hello All!

I've recently encountered a limitation issue with the number of IIf statements I can use within a query field. What other options do I have available when I need to use an IIf statement that could potentially have 30+ expressions?

Code:
Bin Profile: IIf([Max]=0,"REF",IIf([Max]>24,"ExceedsDim",IIf([P4 BIN (5-5|2-75|11)]>=[Slot MaxBank],"P4 BIN",IIf([P3 BIN (5-5|5-8|11)]>=[Slot MaxBank],"P3 BIN",IIf([P2-A BIN (6-2|9-7|23-5)]>=[Slot MaxBank],"P2-A BIN",IIf([P2-B BIN (11|9-7|23-5)]>=[Slot MaxBank],"P2-B BIN",IIf([P2-C BIN (15|9-7|23-5)]>=[Slot MaxBank],"P2-C BIN",IIf([P2-D BIN (15|9-7|29-5)]>=[Slot MaxBank],"P2-D BIN",IIf([P2-E BIN (23-5|9-7|29-5)]>=[Slot MaxBank],"P2-E BIN",IIf([P2-F BIN (47-5|9-7|29-5)]>=[Slot MaxBank],"P2-F BIN",IIf([P1-A BIN (23-5|16-7|29-5)]>=[Slot MaxBank],"P1-A BIN",IIf([P1 BIN (47-5|16-7|29-5)]>=[Slot MaxBank],"P1 BIN","ExceedsBins"))))))))))))
Thanks so much for your assistance

2. Registered User
Join Date
Nov 2011
Posts
413
Try using a Case Statement instead.Example:

Select Case (YrCount)
Case "First"
Me.Filter = [lblcountofyears].Caption = "First Year Requirement"
Case "Second"
Case "All"
DoCmd.RunCommand acCmdRemoveAllFilters
End Select
Me.FilterOn = True
HTH

3. Registered User
Join Date
Jan 2013
Posts
53
Can I incorporate this into my SQL statement?

4. Registered User
Join Date
Nov 2011
Posts
413
You said that you could have a lot of If Statements, hence the Case code. Replace your If Statement(s) with the Case Code as you see fit.

5. Moderator
Join Date
Mar 2009
Posts
5,442
Originally Posted by VBAnewbie
Can I incorporate this into my SQL statement?
No, you can't, not in Access SQL.

6. Registered User
Join Date
Jan 2013
Posts
53
Ok so I'm having a bit of trouble understanding how to incorporate the Case Statement into my qry. This is what my expression currently looks like

Suggested Insert: Switch([Max]>47,"ExceedsDim",[P1 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P1-A RACK GLASS (20|32-625|48)]>=[Slot MaxBank],"20|32.625|48",[P2 RACK A A (50|48-625|48)]>=[Slot MaxBank],"50|48.625|48",[P2 RACK B C (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P3 HAZ (20|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK A B (33-3|24-625|48)]>=[Slot MaxBank],"33.3|24.625|48",[P3 RACK C C (25|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK D D (25|24-625|48)]>=[Slot MaxBank],"25|24.625|48",[P4 RACK A A (50|60-625|48)]>=[Slot MaxBank],"50|60.625|48",[P4 RACK B B (50|54-625|48)]>=[Slot MaxBank],"50|54.625|48",[P5 RACK A B (50|70-625|48)]>=[Slot MaxBank],"50|70.625|48",[P5 RACK ODD A B (50|70-625|60)]>=[Slot MaxBank],"50|70.625|48",[P6 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48"),[P7 EXAUST (12-5|86-625|48)]>=[Slot MaxBank],"12.5|86.625|48",[P8 PANEL (20|38-625|48)]>=[Slot MaxBank],"20|86.625|48",[P9 RACK (20|14-625|48)]>=[Slot MaxBank],"20|14.625|48"),[SC RADIO RACK A A (50|48-625|48)]>=[Slot MaxBank],"50|48.625|48",[SC RADIO RACK B C (20|24-625|48)]>=[Slot MaxBank],"20|24.625|48")
How should I start the vba code? Also what would the new expression look like?

Here is what my qry currently looks like:
SELECT [1_MaxMedMin].[Part Number], Switch([Max]>47,"ExceedsDim",[P1 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P1-A RACK GLASS (20|32-625|48)]>=[Slot MaxBank],"20|32.625|48",[P2 RACK A A (50|48-625|48)]>=[Slot MaxBank],"50|48.625|48",[P2 RACK B C (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P3 HAZ (20|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK A B (33-3|24-625|48)]>=[Slot MaxBank],"33.3|24.625|48",[P3 RACK C C (25|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK D D (25|24-625|48)]>=[Slot MaxBank],"25|24.625|48",[P4 RACK A A (50|60-625|48)]>=[Slot MaxBank],"50|60.625|48",[P4 RACK B B (50|54-625|48)]>=[Slot MaxBank],"50|54.625|48",[P5 RACK A B (50|70-625|48)]>=[Slot MaxBank],"50|70.625|48",[P5 RACK ODD A B (50|70-625|60)]>=[Slot MaxBank],"50|70.625|48",[P6 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48") AS [Suggested Insert], "" AS [Bin Profile], [1_MaxMedMin].Length, [1_MaxMedMin].Width, [1_MaxMedMin].Height
FROM ([4R_Slot MaxBank] INNER JOIN 1_MaxMedMin ON [4R_Slot MaxBank].[Part Number] = [1_MaxMedMin].[Part Number]) INNER JOIN [3R_Bin Size] ON ([4R_Slot MaxBank].[Part Number] = [3R_Bin Size].[Part Number]) AND ([1_MaxMedMin].[Part Number] = [3R_Bin Size].[Part Number])
GROUP BY [1_MaxMedMin].[Part Number], Switch([Max]>47,"ExceedsDim",[P1 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P1-A RACK GLASS (20|32-625|48)]>=[Slot MaxBank],"20|32.625|48",[P2 RACK A A (50|48-625|48)]>=[Slot MaxBank],"50|48.625|48",[P2 RACK B C (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48",[P3 HAZ (20|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK A B (33-3|24-625|48)]>=[Slot MaxBank],"33.3|24.625|48",[P3 RACK C C (25|24-625|48)]>=[Slot MaxBank],"20|24.625|48",[P3 RACK D D (25|24-625|48)]>=[Slot MaxBank],"25|24.625|48",[P4 RACK A A (50|60-625|48)]>=[Slot MaxBank],"50|60.625|48",[P4 RACK B B (50|54-625|48)]>=[Slot MaxBank],"50|54.625|48",[P5 RACK A B (50|70-625|48)]>=[Slot MaxBank],"50|70.625|48",[P5 RACK ODD A B (50|70-625|60)]>=[Slot MaxBank],"50|70.625|48",[P6 RACK (33-3|32-625|48)]>=[Slot MaxBank],"33.3|32.625|48"), "", [1_MaxMedMin].Length, [1_MaxMedMin].Width, [1_MaxMedMin].Height;

7. Registered User
Join Date
Nov 2011
Posts
413
I believe Sinndho said you could not. If your capable of writing that kind of If statement, then the Case statement should be easy.
Last edited by Burrina; 10-03-13 at 17:24. Reason: Further Explanation

8. Registered User
Join Date
Apr 2004
Location
metro Detroit
Posts
634
This can not be done in the query builder. You can probably use a custom function and/or look up table. If you can explain in plain English what you're trying to do we may be able to help you figure out how to do it.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•