# Thread: Help please on IIf syntax

1. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90

## Unanswered: Help please on IIf syntax

Hi, I'm sure this is going to be a quick answer for somebody, would be grateful for any assistance.

In a query, I need to do a calculation, based on the result of a calculation in the same query.

[Total] is the existing calculation, and if total = 7, then I want it multiplied by 4, or if the total is 14, then I want it multiplied by 7.

Here's what I got, and it doesn't work.

=IIf([Total] = "7" *4, "14"*7)

many, many thanks

2. Registered User
Join Date
Feb 2004
Posts
40
You will have to get rid of the quotes, this is text which can not be used by a calculation.
If this a text field use a function like CINT to convert it from text to integer.

From then on you can calculate.

3. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90
... which gives me

=IIf([Total] = 7 *4, 14*7)

which still doesn't work. The '7' and the '14' are number fields.

4. Registered User
Join Date
Jun 2007
Location
Maitland NSW,Australia
Posts
396
=IIf([Total] = "7" *4, "14"*7)
In the above you are assuming that there are only two values for total. What happens when the total is not 7 or 14?

Have you tried
=IIf([Total] = 7,([total] *4), iif([total]=14,([total]*7),[total]))

5. Registered User
Join Date
May 2009
Posts
258
Since you need to process multiple cases, it'd be better to use the Switch function:
Code:
`=[Total] * Switch([Total]=7, 4, [Total]=14, 7)`
Regards,

Ax

6. L33t Helpa Munky
Join Date
Nov 2007
Location
Adelaide, South Australia
Posts
4,049
Agreed with the Smurf

7. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90
Thanks for the replies. I fear that I gave you bad info in the first place, the 'Total' field I was referring to is actually named 'Number_Of_Days'. It is a number field, and will only ever contain the numbers 7 and 14.

I have replaced the word 'total' with 'Number_Of_Days' in your suggestions, and got the following error messages.

=[Number_Of_Days] * Switch([Number_Of_Days]=7, 4, [Number_Of_Days]=14, 7)

Invalid syntax

=IIf([Number_Of_Days] = 7,([Number_Of_Days] *4), iif([Number_Of_Days]=14,([Number_Of_Daysl]*7),[total]))

and then

=IIf([Number_Of_Days] = 7,([total] *4), iif([Number_Of_Days]=14,([total]*7),[total]))

The expression you entered has an invalid . ! or parenthesis

Thanks for any further info, I'm not going to manage this on my own...

Lisa

8. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
what do you think is wrong with this line
Code:
`=IIf([Number_Of_Days] = 7,([Number_Of_Days] *4), iif([Number_Of_Days]=14,([Number_Of_Daysl]*7),[total]))`
?
as far as I can see there is no significant issue witrh this as air code, but in using in your application there probably will be a problem

have you looked in detail at what the help system suggests for the IIF function

9. Registered User
Join Date
Feb 2004
Posts
40
hi, i just tried it on a temporary database and this statemant in a query is working ( using access 2007 ):

Expr1: IIf([Table1]![number_of_days]=7;[Table1]![number_of_days]*4;IIf([Table1]![number_of_days]=14;[Table1]![number_of_days]*7;[Table1]![total]))

The things which are diffent are :

- table mentioning
- no () around your calculations
- ; instead of , ( that could be qeury related )

Are you using this in a query or in code ??
Regards

10. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90
In query. I will check out your sample database, many thanks for that. L

11. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90
Ok, I made a little tweak because the information I gave you was wrong, and it works perfectly. THANKS SO MUCH, now I can get these invoices out and get some money into the company.

12. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
I think you need to develop some better debugging skills
from what I can see I suspect your problem was the [total], which I'm guessing was a hangover from the original code...... had you changed that to [number_of_days] it would probably have worked, and probably have saved you some 4 days of time

13. Registered User
Join Date
May 2009
Location
Crete, Greece
Posts
90
I don't really need to, to be honest. I'm a secretary. I just need to get this db working so I can manage a project.

14. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
if you are developing anything, you need better debug skills
it saves YOU masses of time.

#### Posting Permissions

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