Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: IIf statement limitation

    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. #2
    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. #3
    Join Date
    Jan 2013
    Posts
    53
    Can I incorporate this into my SQL statement?

  4. #4
    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. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by VBAnewbie View Post
    Can I incorporate this into my SQL statement?
    No, you can't, not in Access SQL.
    Have a nice day!

  6. #6
    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. #7
    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 18:24. Reason: Further Explanation

  8. #8
    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
  •