Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Expression in a query

    I am trying to create an expression in a query.

    Depending on what the period the user selects the expression will return a different sum.

    Example:

    User selects
    · “Period 1” this returns the sum “BALANCE_BF”
    · “Period 2” this returns the sum “BALANCE_BF + BALANCE_MTH1”
    · “Period 3” this returns the sum “BALANCE_BF + BALANCE_MTH1 + BALANCE_MTH2”

    I have created the following that does not work, I get a message “wrong number of arguments”


    cumact1:IIf([grg_period].[period]= 1,( [NOMINAL_LEDGER].[BALANCE_BF]), [financial_period].[period]= 2, ([NOMINAL_LEDGER].[BALANCE_BF]+ [NOMINAL_LEDGER].[BALANCE_MTH1]))

    I have attached a Word Doc maybe easier to read.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool wrong syntax format

    iif (x=1,"ONE",iif(x=2,"TWO",iif(x=3,"Three","Higher") ))


    or

    iif (x=1,"ONE",iif(x=2,"TWO","Three"))

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You can also try the Switch() function
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    What is the switch function?

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

    Syntax

    Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

    The Switch function syntax has these parts:

    Part Description
    expr Required. Variant expression you want to evaluate.
    value Required. Value or expression to be returned if the corresponding expression is True.


    I can't find Switch using the database menu help, but it comes up from the project window help.

    It basically saves you from having to do nested IIfs, which usually begin to confuse me after about 3.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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