Results 1 to 6 of 6
  1. #1
    kendra is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    3

    nested if statement with two criteria

    I have an iif formula that I can't seem to get to work. It is really long but not all that complex. What I want the query to do is look at the supplier's country and compare to the delivery country. If the combination is a certain pair I want the number that I designate to be returned in the query. Here is the formula that I have right now. I get no results. There is data in the the table that I am pulling from. Can anyone help? Thanks in advance!



    IIf([supplier country]="us" And [delivery country]="us","0",IIf([supplier country]="us" And [delivery country]="mx","48",IIf([supplier country]="us" And [delivery country]="ca","24",IIf([supplier country]="mx" And [delivery country]="mx","0",IIf([supplier country]="mx" And [delivery country]="us","48",IIf([supplier country]="mx" And [delivery country]="ca","72",IIf([supplier country]="ca" And [delivery country]="ca","0",IIf([supplier country]="ca" And [delivery country]="us","24",IIf([supplier country]="ca" And [delivery country]="mx","72","missing country designation")))))))))

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about creating a User Defined Public Function (UDF) that return the number when you pass it the two field values. Then use a Select Case structure to return the value. It would be a lot easier to read and maintain. Your new field would be NewField:=YourUDF([supplier country],[delivery country].

  3. #3
    kendra is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    3
    Could you give me an example of what the code would look like? This would be my first attempt at using it for anything. I have been able to accomplish all of my tasks via queries but now am seeing limitations. I really appreciate your help! Kendra

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your UDF will look something like:
    Code:
    Public Function Foo(strSource As String, strDestination As String) As String
       Select Case strSource
          Case "us"
             Select Case strDestination
                Case "us"
                   Foo = "0"
                Case "mx"
                   Foo = "48"
                Case "ca"
                   Foo = "24"
                Case Else
                   Foo = "missing Destination country designation"
             End Select
          Case "mx"
             Select Case strDestination
                Case "us"
                   Foo = "0"
                Case "mx"
                   Foo = "48"
                Case "ca"
                   Foo = "72"
                Case Else
                   Foo = "missing Destination country designation"
             End Select
          Case "ca"
             Select Case strDestination
                Case "us"
                   Foo = "0"
                Case "mx"
                   Foo = "24"
                Case "ca"
                   Foo = "72"
                Case Else
                   Foo = "missing Destination country designation"
             End Select
          Case Else
             Foo = "missing Source country designation"
       End Select
    End Function

  5. #5
    kendra is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    3

    Thanks RuralGuy!

    I am going to try this today. Sorry for the delayed response. Things have been a little crazy here!

    Kendra

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I understand. I'll be around.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to use IF then statement
    By ronnie4 in forum Access
    Replies: 1
    Last Post: 03-17-2009, 11:32 PM
  2. Nested Joins
    By zephaneas in forum Programming
    Replies: 0
    Last Post: 11-10-2008, 11:49 AM
  3. Concatenation, nested IIF functions
    By krymer in forum Queries
    Replies: 2
    Last Post: 10-20-2008, 07:27 AM
  4. Replies: 0
    Last Post: 05-17-2008, 01:18 AM
  5. Nested IIf query will not work
    By ddog171 in forum Queries
    Replies: 1
    Last Post: 06-20-2006, 02:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums