Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can I use nested if statements in Outlook 2007 when creating custom forms?

Posted on 2009-07-13
9
Medium Priority
?
481 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:rpsys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24838878
Hi, rpsys.

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.  
0
 

Author Comment

by:rpsys
ID: 24838907
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
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24838926
I've never tried.  Can you give me an example of the syntax you're using?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:rpsys
ID: 24839221
I would like to be able to change the code below from that to something like:

Switch([Product]="Express",(
               Switch([Request]="Purchase/Instalments", 40,
                            [Request]="Purcase/Outright"       , 400),
             [Product]="Bronze",(
               Switch([Request]="Purchase/Instalments", 80,
                            [Request]="Purcase/Outright"       , 800) +
                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...

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 24839309
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.
0
 

Author Comment

by:rpsys
ID: 24840137
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
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24855418
Ok, you can definitely nest IIF statements.  You simply need to bind the fields to properties.  I tested by creating three text boxes which I bound to fields called F1, F2, and F3.  I then edited the formula of field F3 setting it to this calculation

    IIf([F1] = "A",1,IIf([F1]="B",2,3))

This formula sets the value of F3 to 1 if F1 is set to "A", 2 if F1 is set to "B", and 3 when F1 is set to any other value.
0
 

Author Comment

by:rpsys
ID: 24867781
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.  
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24867976
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question