Results 1 to 8 of 8

Thread: IIF Statements

  1. #1
    Join Date
    Jan 2004
    Posts
    44

    Unanswered: IIF Statements

    Hi there,

    I am using an IIF Statement in an unbound field in a report which is retrieving the data from a query.

    The statement is as follows:-

    =IIf(IsNull([HomeAddress2] And [ActiveHome]=Yes),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],[HomeAddress1] & ", " & [HomeAddress2] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode])

    The problem is, this only checks whether HomeAddress2 is null & ActiveHome = yes and I would like to add on another IIF statement to this.

    i.e.

    =IIf(IsNull([OtherAddress2] And [ActiveOther]=Yes),[OtherAddress1] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode],[OtherAddress1] & ", " & [OtherAddress2] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode])

    If there a way or joining these two IIf statements together. I have tried using the OR but I just get a -1 value.

    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: IIF Statements

    Originally posted by Smilla
    Hi there,

    I am using an IIF Statement in an unbound field in a report which is retrieving the data from a query.

    The statement is as follows:-

    =IIf(IsNull([HomeAddress2] And [ActiveHome]=Yes),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],[HomeAddress1] & ", " & [HomeAddress2] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode])

    The problem is, this only checks whether HomeAddress2 is null & ActiveHome = yes and I would like to add on another IIF statement to this.

    i.e.

    =IIf(IsNull([OtherAddress2] And [ActiveOther]=Yes),[OtherAddress1] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode],[OtherAddress1] & ", " & [OtherAddress2] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode])

    If there a way or joining these two IIf statements together. I have tried using the OR but I just get a -1 value.

    Thanks


    Try checking into the Switch function.

    From the Help files:

    Switch Function


    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.



    Remarks

    The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

    Switch returns a Null value if:

    None of the expressions is True.


    The first True expression has a corresponding value that is Null.
    Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

    Gregg

  3. #3
    Join Date
    Jan 2004
    Posts
    44
    Thank you,

    I'm having a little difficulty arranging the expressions.

    Here is what I have done, could you point out where i am going wrong please.

    = Switch([ActiveHome]=Yes, [HomeAddress1], [ActiveOther]=Yes, [OtherAddress1], [ActiveWork]=Yes,[WorkAddress1]])
    Switch([ActiveHome]=Yes, [HomeAddress1], [ActiveOther]=Yes, [OtherAddress1], [ActiveWork]=Yes,[WorkAddress1])

    Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question

    Originally posted by Smilla
    Thank you,

    I'm having a little difficulty arranging the expressions.

    Here is what I have done, could you point out where i am going wrong please.

    = Switch([ActiveHome]=Yes, [HomeAddress1], [ActiveOther]=Yes, [OtherAddress1], [ActiveWork]=Yes,[WorkAddress1]])
    Switch([ActiveHome]=Yes, [HomeAddress1], [ActiveOther]=Yes, [OtherAddress1], [ActiveWork]=Yes,[WorkAddress1])

    Thanks

    The switch function works in a similar fashion to the IIf function in that you would specify each of your conditions (which may include multiple conditions using AND and OR) followed by a comma and what you want to return if the condition evaluates to true. Sounds like you already had a pretty good idea of how to do that.

    If you still need some help, could you describe each of the conditions and the results that you want to achieve in normal English vs. the expression version?

    Gregg

  5. #5
    Join Date
    Jan 2004
    Posts
    44
    Thanks Gregg,

    What I am trying to achieve is this:-

    I have four tables, each with an ID to link to each other. The first table has an employees personal details, the second with their home address, the third with their Work Address and the fourth as Another Address. Each table has an Active/Inactive Yes/No field.

    I have one form which is looking at all fours tables and the user can click the relevant Active/Inactive check boxes to differentiate which address is the current active one.

    In my report, i have an unbound field which would like to pick out the current active address.

    For instance, what I was using before
    =IIf(IsNull([HomeAddress2] And [ActiveHome]=Yes),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],[HomeAddress1] & ", " & [HomeAddress2] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode])

    This checks whether HomeAddress2 is blank and ActiveHome (if its active or not) = yes.

    Obviously this is just one expression and it gives me one result but how can I print the home details if ActiveHome = yes OR print the work details if ActiveWork = Yes etc etc.

    Can I do this in one expression i.e Switch Function (by the way, I cannot find any info about this function on the web).

    One other thing - I am designing this in Access 97 arghh!!!

    Thanks for your help

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Smilla
    Thanks Gregg,

    What I am trying to achieve is this:-

    I have four tables, each with an ID to link to each other. The first table has an employees personal details, the second with their home address, the third with their Work Address and the fourth as Another Address. Each table has an Active/Inactive Yes/No field.

    I have one form which is looking at all fours tables and the user can click the relevant Active/Inactive check boxes to differentiate which address is the current active one.

    In my report, i have an unbound field which would like to pick out the current active address.

    For instance, what I was using before
    =IIf(IsNull([HomeAddress2] And [ActiveHome]=Yes),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],[HomeAddress1] & ", " & [HomeAddress2] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode])

    This checks whether HomeAddress2 is blank and ActiveHome (if its active or not) = yes.

    Obviously this is just one expression and it gives me one result but how can I print the home details if ActiveHome = yes OR print the work details if ActiveWork = Yes etc etc.

    Can I do this in one expression i.e Switch Function (by the way, I cannot find any info about this function on the web).

    One other thing - I am designing this in Access 97 arghh!!!

    Thanks for your help

    What I gave you came from the VBA help files. If you use your object browser (F2 in the code window of any module) type in switch and search for it. Hit F1 when it finds what you are looking for.

    I think that what you are doing has the potential to be complex the way you have your information structured. At least for one built in function. It can be done though. It's a similar pattern to what you were using for the IIf function except that the Switch function evaluates each condition for being True only. If this is true display this result, If that is true display that result etc...

    Example:

    =Switch(IsNull(Table1, Field1) And Table1.Field2 = True, Result1, IsNull(Table2.Field1) And Table2.Field2 = False, Result2)

    Something along those line except continue to add statements to be evaluated in pairs of contition/result separated by commas.

    Try it with some simple examples in the QBE grid to get a feel for it. I use it a fair bit when I'm trying to avoid writing code for someone.

    Experiment and call back later with the results.

    Gregg

  7. #7
    Join Date
    Jan 2004
    Posts
    44
    Thanks again Gregg, i am so close ..........

    Just another quick question, can I use ISNOTNULL. A parameter error comes up when I type this in, any suggestions.

    Regards

  8. #8
    Join Date
    Jan 2004
    Posts
    44
    Its ok, I've worked it out - thanks again for all your help.

    If anyone is interested, this was my code (a bit dazzling on the eyes but it works!!)

    =Switch(IsNull([HomeAddress2] And [ActiveHome]=True),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],([HomeAddress2]<>" " And [ActiveHome]=True),[HomeAddress1] & ", " & [HomeAddress2] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & [HomePostCode],IsNull([OtherAddress2] And [ActiveOther]=True),[OtherAddress1] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode],([OtherAddress2]<>" " And [ActiveOther]=True),[OtherAddress1] & ", " & [OtherAddress2] & ", " & [OtherTown] & ", " & [OtherCounty] & ", " & [OtherPostCode],IsNull([WorkAddress2] And [ActiveWork]=True),[WorkAddress1] & ", " & [WorkTown] & ", " & [WorkCounty] & ", " & [WorkPostCode],([WorkAddress2]<>" " And [ActiveWork]=True),[WorkAddress1] & ", " & [WorkAddress2] & ", " & [WorkTown] & ", " & [WorkCounty] & ", " & [WorkPostCode])
    Last edited by Smilla; 02-18-04 at 13:51.

Posting Permissions

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