Can I use nested if statements in Outlook 2007 when creating custom forms?
Dear Experts,
I'm trying to make a request template for staff to fill in when a customer makes a request. As part of this template I would like to calculate the cost automatically as this will save a lot of time and effort, but in order to do this I need to enter the calculation in as a formula which has nested If statements within other If's and Switch statements. Is it possible to do this in Outlook 2007? So far I am not using VB directly, Im just right clicking and going to properties, and then Im setting the formula from there.
I'm trying to make a request template for staff to fill in when a customer makes a request. As part of this template I would like to calculate the cost automatically as this will save a lot of time and effort, but in order to do this I need to enter the calculation in as a formula which has nested If statements within other If's and Switch statements. Is it possible to do this in Outlook 2007? So far I am not using VB directly, Im just right clicking and going to properties, and then Im setting the formula from there.
ASKER
Can you use the immediate IIf with in and IIf statement though? It doesn't generate an error when I try but no result if genertaed either which is what confuses me
I've never tried. Can you give me an example of the syntax you're using?
ASKER
I would like to be able to change the code below from that to something like:
Switch([Product]="Express"
Switch([Request]="Purchase
[Request]="Purcase/Outrigh
[Product]="Bronze",(
Switch([Request]="Purchase
[Request]="Purcase/Outrigh
IIf([Firewalls], 250, 0) + IIf([Photo Edit], 200, 0) etc...
)
This will make things a lot easier and will make further expansion possible,
I'm just not sure you can use this method, which would be unfortunate,
When i'm entering these formulas i'm using the edit formula box in the design editor, not in VBA or VBS because I have no idea how to use them with the form.
Switch([Product]="Express"
Switch([Request]="Purchase
[Request]="Purcase/Outrigh
[Product]="Bronze",(
Switch([Request]="Purchase
[Request]="Purcase/Outrigh
IIf([Firewalls], 250, 0) + IIf([Photo Edit], 200, 0) etc...
)
This will make things a lot easier and will make further expansion possible,
I'm just not sure you can use this method, which would be unfortunate,
When i'm entering these formulas i'm using the edit formula box in the design editor, not in VBA or VBS because I have no idea how to use them with the form.
Switch([Product]="Express" And [Request]="Purchase/Instalments", 40, 0,
[Product]="Silver" And [Request]="Purchase/Instalments", 80, 0) + Switch([Product]="Express" And [Request]="Purchase/Outright", 400, 0,
[Product]="Silver" And [Request]="Purchase/Outright", 800, 0) * [Quantity] +
IIf([Firewalls] And ([Product]="Bronze" Or [Product]="Silver"), 250, 0) +
IIf([Photo Edit] And ([Product]="Bronze" Or [Product]="Silver"), 200, 0)
etc...
To the best of my knowledge VBScript does not have a command/function called "Switch". It has the CASE ... END CASE statement and as I noted above I don't beleive that'll work in a formula. A nested IIF wouldn't look like the example you gave. It would look like
IIf(A Condition, True Part, IIf(Another Condition, True Part, False Part))
The outter IIf is evaluated first. If it is false, then the second IIf is evaluated.
IIf(A Condition, True Part, IIf(Another Condition, True Part, False Part))
The outter IIf is evaluated first. If it is false, then the second IIf is evaluated.
ASKER
I don't think it is VBScript, but if you go to a formula box, click "edit..." and then look under 'function' and then 'general' it has a switch statement described as Switch(Expr1, Expr1, Expr2, Expr2). :S
But if you try and nest any statements, say you put an iif inside a Switch to change the Text of a box automatically, all that gets displayed is todays date, and a time of 08:00:00 which isn't very helpful...
I've attached a picture file to explain what I mean, it might help you understand what i'm doing :)
Thanks
Outlook-forms-with-formula---pro.JPG
But if you try and nest any statements, say you put an iif inside a Switch to change the Text of a box automatically, all that gets displayed is todays date, and a time of 08:00:00 which isn't very helpful...
I've attached a picture file to explain what I mean, it might help you understand what i'm doing :)
Thanks
Outlook-forms-with-formula---pro.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool that works great :) One question which I know I didn't mention in my main question above but is linked, what about IIf statements and Switch statements with in Swtich statements? I just seem to get the current date with a set time, usually 08:00:00, as shown above in my picture when i implement a nested switch.
I'll have to test, I've never used SWITCH before. It appears to be designed to test a single property and return a result based on the value. In other words, it behaves just like a SELECT CASE statement. Neither allow you to conduct additional tests. The example Microsoft gives in the documentation for Switch is
Switch(CityName = "London", "English", CityName _
= "Rome", "Italian", CityName = "Paris", "French")
The test is only checking the value of CityName and returning the country name based on the name of the city. I don't believe it is designed to work with an IIF nested inside.
Switch(CityName = "London", "English", CityName _
= "Rome", "Italian", CityName = "Paris", "French")
The test is only checking the value of CityName and returning the country name based on the name of the city. I don't believe it is designed to work with an IIF nested inside.
According to Microsoft's examples you should be able to use the "immediate if" (i.e. IIF) function in a formula. I don't believe a CASE ... END CASE will work.