# Thread: Expression in a query

1. Registered User
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.

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

## 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. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
You can also try the Switch() function

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

5. Registered User
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.

#### Posting Permissions

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