带有嵌套IIF语句的TextBox控件源将返回相同的列，无论 [英] TextBox Control Source with Nested IIF Statement is Returning Same Column Regardless

问题描述

我对Access不太了解，我没有创建此Access数据库，因此请耐心等待，但是我会尽力提供所有必要的信息.

I'm not very experienced with Access, and I did not create this Access database, so please bear with me, but I will try to give all the necessary information.

报表中有一个文本框，应根据另一列的值返回3个可能的列之一的值.

There is a textbox in a Report that should return the value from one of 3 possible columns based on the value of another column.

简而言之，如果津贴类型为按个案"，则文本框值=个案列中的值

In short, if Allowance Type is "Per Case" Then TextBox Value = Value From Case Column

这是我最近在控制源"中尝试过的语句:

Here is the statement I have most recently tried in the Control Source:

=IIf([Allowance Type]="% Off Invoice",[Freight Paid % OI],IIf([Allowance Type]="Per Pound",[Freight Paid / LB],IIf([Allowance Type]="Per Case",[Freight Paid/_Case],"")))

在此示例中，无论配额类型如何，每次都会返回运费已付/LB"列中的值.

In this example, the value from the "Freight Paid / LB" column is returned every time no matter the Allowance Type.

知道我在做什么错吗?

Any idea what I'm doing wrong?

已使用的3列是运费已付％OI"，运费已付/LB"，运费已付/_Case"，并且应与津贴类型"相对应.

Freight Paid % OI, Freight Paid / LB, Freight Paid/_Case are the 3 columns used, and which is used should correspond to the Allowance Type.

此特定尝试的参考来自 https://access -programmers.co.uk/forums/showthread.php?t=192667

The reference for this specific attempt was from https://access-programmers.co.uk/forums/showthread.php?t=192667

推荐答案

如注释中所述，假定所有引用的字段名称都是正确的，我看不到您的IIf语句的代码中存在语法错误，并且然后，我们还要确定测试表达式是否已按预期验证.

As discussed in the comments, assuming that all of the referenced field names are correct, I see no syntax errors present in the code for your IIf statement and we have then also ascertained that the test expressions are being validated as expected.

=IIf
(
    [Allowance Type]="% Off Invoice",
    [Freight Paid % OI],
    IIf
    (
        [Allowance Type]="Per Pound",
        [Freight Paid / LB],
        IIf
        (
            [Allowance Type]="Per Case",
            [Freight Paid/_Case],
            ""
        )
    )
)

作为替代建议，您可以尝试使用更简单的switch语句，从而避免使用嵌套表达式:

As an alternative suggestion, you could try using a simpler switch statement, which avoids the need for nested expressions:

=Switch([Allowance Type] = "% Off Invoice", [Freight Paid % OI], [Allowance Type] = "Per Pound", [Freight Paid / LB], [Allowance Type] = "Per Case", [Freight Paid/_Case], True, "")

它的结构如下:

=Switch
(
    [Allowance Type] = "% Off Invoice", [Freight Paid % OI], 
    [Allowance Type] = "Per Pound",     [Freight Paid / LB], 
    [Allowance Type] = "Per Case",      [Freight Paid/_Case],
    True, ""
)

这篇关于带有嵌套IIF语句的TextBox控件源将返回相同的列，无论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！