IIf语句太复杂

Question

我正尝试在MS Access 2003中编写一个相当长的IIf语句，但当我插入bits时，它建议我它太复杂了：

MIN_PASS_FAIL: IIf(
  [MIN CAR RESPONSE CODE] = "0033"
, "ACCEPTED"
, IIf(
    [MIN CAR RESPONSE CODE] = "0037"
  , "ACCEPTED"
  , IIf(
      [MIN CAR RESPONSE CODE] = "0038"
    , "ACCEPTED"
    , IIf(
        [MIN CAR RESPONSE CODE] = "0039"
      , "ACCEPTED"
      , IIf(
          [MIN CAR RESPONSE CODE] = "0040"
        , "ACCEPTED"
        , IIf(
            [MIN CAR RESPONSE CODE] = "0055"
          , "ACCEPTED"
          , IIf(
              [MIN CAR RESPONSE CODE] = "0056"
            , "ACCEPTED"
            , IIf(
                [MIN CAR RESPONSE CODE] = "0061"
              , "ACCEPTED"
              , IIf(
                  [MIN CAR RESPONSE CODE] = "0073"
                , "ACCEPTED"
                , Iif(
                    [MIN CAR RESPONSE CODE] = "0013"
                  , "INFORMATION"
                  , Iif(
                      [MIN CAR RESPONSE CODE] = "0018"
                    , "INFORMATION"
                    , Iif(
                        [MIN CAR RESPONSE CODE] = "0019"
                      , "INFORMATION"
                      , Iif(
                          [MIN CAR RESPONSE CODE] = "0028"
                        , "INFORMATION"
                        , Iif(
                            [MIN CAR RESPONSE CODE] = "0045"
                          , "INFORMATION"
                          , Iif(
                              [MIN CAR RESPONSE CODE] = "0046"
                            , "INFORMATION"
                            , Iif(
                                [MIN CAR RESPONSE CODE] = "0047"
                              , "INFORMATION"
                              , Iif(
                                  [MIN CAR RESPONSE CODE] = "0048"
                                , "INFORMATION"
                                , Iif(
                                    [MIN CAR RESPONSE CODE] = "0060"
                                  , "INFORMATION"
                                  , "REJECTED"
                                  )
                                )
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

我需要三个输出，“接受”、“拒绝”和“信息”..

我该如何解决这个问题呢？

Answer 1

你有一组代码映射到“接受”，另一组映射到“信息”。任何其他代码都应该映射到"REJECTED“。

使用下表来存储前两个集合的映射。然后，您可以使用DLookup检索前两个集合中代码的response_text值。

DLookup("response_text", "YourTableNameHere", "response_code='" & [MIN CAR RESPONSE CODE] & "'")

对于"REJECTED“代码，DLookup将返回Null。因此，您可以使用Nz()替换Null的"REJECTED“。

Nz(DLookup("response_text", "YourTableNameHere", "response_code='" & [MIN CAR RESPONSE CODE] & "'"), "REJECTED")

类似的方法应该适用于Access VBA代码或从Access会话中运行的查询。如果要在将在Access会话外部运行的查询中使用它，则可以使用IIf表达式而不是Nz。

但是，如果这是针对查询的，请考虑将主数据表与映射表进行左连接。那么你甚至不需要DLookup..。

SELECT
    main.[MIN CAR RESPONSE CODE],
    Nz(ytnh.response_text, 'REJECTED') AS MIN_PASS_FAIL
FROM
    MainTable AS main
    LEFT JOIN YourTableNameHere AS ytnh
    ON main.[MIN CAR RESPONSE CODE] = ytnh.response_code;

映射表...

response_code response_text
0033          ACCEPTED
0037          ACCEPTED
0038          ACCEPTED
0039          ACCEPTED
0040          ACCEPTED
0055          ACCEPTED
0056          ACCEPTED
0061          ACCEPTED
0073          ACCEPTED
0013          INFORMATION
0018          INFORMATION
0019          INFORMATION
0028          INFORMATION
0045          INFORMATION
0046          INFORMATION
0047          INFORMATION
0048          INFORMATION
0060          INFORMATION

Answer 2

HansUp将其放入查找表的答案很好。特别是如果他们可能会改变的话。对于不需要查找表的答案，可以使用InStr函数。试试这个：

MIN_PASS_FAIL: IIF(InStr("0033.0037.0038.0039.0040.0055.0056",[MIN CAR RESPONSE CODE]),"ACCEPTED",IIf(InStr("0013.0018.0019.0028.0045.0046.0047.0048.0060",[MIN CAR RESPONSE CODE]),"ACCEPTED",IIf(InStr("0013.0018.0019.0028.0045.0046.0047.0048.0060",[MIN CAR RESPONSE CODE]),"INFORMATION","REJECTED")))

Answer 3

HansUp的答案绝对是最好的，因为你有这么多的条目要查找。我将指出另一种选择，因为它在其他情况下可能很方便。

因为您总是测试同一个字段，所以可以使用SQL in运算符：

MIN_PASS_FAIL: IIf(
  [MIN CAR RESPONSE CODE] in (
    "0033"
  , "0037"
  , "0038"
  , ...
  )
, "ACCEPTED"
, Iif(
    [MIN CAR RESPONSE CODE] in (
      "0013"
    , "0018"
    , "0019"
    , ...
    )
  , "INFORMATION"
  , "REJECTED"
  )
)

in运算符测试该值是否是值列表中的一个。这使您的代码保持相对扁平化。实际上，您可以通过使用Switch函数而不是Iif函数来完全展平它：

MIN_PASS_FAIL: Switch(
  [MIN CAR RESPONSE CODE] in (
    "0033"
  , "0037"
  , "0038"
  , ...
  )
, "ACCEPTED"
, [MIN CAR RESPONSE CODE] in (
    "0013"
  , "0018"
  , "0019"
  , ...
  )
, "INFORMATION"
, True
, "REJECTED"
)

您可以很容易地查找Switch函数的详细信息，但基本上它的工作方式类似于其他语言中的case语句或switch语句，但是SQL是面向表达式的，所以您总是返回一个值。