IIF Help Plz

[tennisbuck]

Hello-

How would I link these two strings together. I want to switch from Zone 2 to Zone 3


Test: IIf([Zone]=2 And [RoundUp]=1,5.49,IIf([Zone]=2 And [RoundUp]=2,5.84,IIf([Zone]=2 And [RoundUp]=3,5.93,IIf([Zone]=2 And [RoundUp]=4,6.07,IIf([Zone]=2 And [RoundUp]=5,6.25,IIf([Zone]=2 And [RoundUp]=6,6.42,IIf([Zone]=2 And [RoundUp]=7,6.73,IIf([Zone]=2 And [RoundUp]=8,7,IIf([Zone]=2 And [RoundUp]=9,7.14,IIf[Zone]=3 And [RoundUp]=1,5.83, IIf([Zone]=3 And [RoundUp]=2,6.22, IIf([Zone]=3 And [RoundUp]=2,6.49, IIf([Zone]=3 And [RoundUp]=4,6.68, IIf([Zone]=3 And [RoundUp]=5,6.77, IIf([Zone]=3 And [RoundUp]=6,6.97, IIf([Zone]=3 And [RoundUp]=7,7.17, IIf([Zone]=3 And [RoundUp]=8,7.36, IIf([Zone]=3 And [RoundUp]=9,7.5

[RuralGuy]

Have you considered using a User Defined Function (UDF) in a standard module with maybe a Select Case structure?

[tennisbuck]

I wish I could build what you described but that would be fairly advance for me.

[RuralGuy]

Can you get a UDF started in a standard module?

[tennisbuck]

No, I can't write SQL.

[RuralGuy]

No SQL involved. Just VBA. Are you willing to try?

[tennisbuck]

Yep, Ill try to learn anything.

[RuralGuy]

Review this link and see if you can get the procedure started inn a standard module. Post back when done or stuck.
http://www.baldyweb.com/Function.htm

[tennisbuck]

I guess I just don't get the link instuctions. I've never wrote VBA either. Shouldn't that iff function work?

[RuralGuy]

IIF statements work but thay can be confusing and only nested 7 levels deep. Here's a rough idea of how the UDF would look in a standard module. You can see it is easier to read than an IIF statement and more importantly, much easier to maintain years later and does not have the nesting limit.

Public Function Foo(Zone As Single, RoundUp As Single) As Single
   Select Case Zone
   
      Case 2
      Select Case RoundUp
         Case 1
            Foo = 5.49
         Case 2
            Foo = 5.84
         Case 3
            Foo = 5.93
         Case 4
            Foo = 6.07
         Case 5
            Foo = 6.25
         Case 6
            Foo = 6.42
         Case 7
            Foo = 6.73
         Case 8
            Foo = 7
         Case 9
            Foo = 7.14
      End Select
   
      Case 3
      Select Case RoundUp
         Case 1
            Foo = 5.83
         Case 2
            Foo = 6.22
         Case 3
            Foo = 6.49
         Case 4
            Foo = 6.68
         Case 5
            Foo = 6.77
         Case 6
            Foo = 6.97
         Case 7
            Foo = 7.17
         Case 8
            Foo = 7.36
         Case 9
            Foo = 7.5
      End Select
   
   End Select
End Function

[tennisbuck]

Thanks Rural Guy. That helps out a lot since I have to add Zone 4-7 now. Where do I paste this code to see if everything jives.

[RuralGuy]

It needs to go in a standard module. Do you know how to start one. It *MUST* be named differently than any procedure it contains. I usually call mine basFunctions. You would call it in your query or Form with:
Test:= Foo([Zone],[RoundUp])
Feel free to name the function something useful instead of Foo!

[tennisbuck]

Thanks Again. I've pasted it into and its working with me adding additonal intel!!

[RuralGuy]

Outstanding! Thanks for posting back with your success.