IIf Statement in VBA

Ste4en

Registered User.
Local time
Today, 13:34
Joined
Sep 19, 2001
Messages
142
Please help me with some code, i have a nasty IIF statement with 5 levels and getting confusing. Seems like code might be easier. How would I represent the following example in a nested iff in VBA. How would I call that function fromm a query.

Gauge: IIf(check=1,"test1", iif(check=2,"test2","not Found")

Thanks
 
Ste4en said:
Please help me with some code, i have a nasty IIF statement with 5 levels and getting confusing. Seems like code might be easier. How would I represent the following example in a nested iff in VBA. How would I call that function fromm a query.

Gauge: IIf(check=1,"test1", iif(check=2,"test2","not Found")

Thanks

Don't do a nested Iif in VBA - there won't be any noticeable speed difference as each side of an IIf statement is evaluated.

What you want is a function with a Select Case structure:

Code:
Public Function GetGauge(ByVal bytValue As Byte) As String
    Select Case bytValue
        Case Is = 1
            GetGauge = "test1"
        Case Is = 2
            GetGauge = "test2"
        Case Is = 3
            GetGauge = "test3"
        Case Is = 4
            GetGauge = "test4"
        Case Else
            GetGauge = "Not found"
    End Select
End Function


Call it in the query like so:

Gauge: GetGauge([Check])
 
Thanks I seem to have simplified my example too much, there are different tests....

Gauge: IIf(check=1,"test1", iif(anothercheck=2,"test2","not Found")

Thanks
 
Post the whole thing then and we'll see if we can construct a function based on the complete logic.
 
Here is one example, I need to add 2 IIF's more to this one

Terms: IIf(InStr([cable type],"CABLE")>0,10,2*(IIf((InStr(Left([cable type],6),"c"))<>0,(Left([cable type],(InStr([cable type],"c")-1))),IIf((InStr(Left([cable type],6),"T"))<>0,(Left([cable type],(InStr([cable type],"T"))-1)),(IIf((InStr(Left([cable type],6),"P"))<>0,(Left([cable type],(InStr([cable type],"P"))-1)),"no Match"))))))

Thanks
 
I've looked and looked at it but I keep getting lost in its repetitiveness.

What's the logic supposed to be?
 
Thanks for staying with me, I have to make a long drive home now before the hurricane traps me, i will post later.

Steve
 
OK, I am back. Here is an example of teh data I am dealing with. The left hand string is a cable identifier, what I want out of the string is to the right. The easy way would be to get the data in a more manageable way, but that will not happen. My IIf statement posted earlier is looking for certain clues in the string and depending on that selecting the returned value.

12Cx14+G MC (C) returns 14
1Cx#4/O GB returns 4/0
1Cx500 TC (M) returns 500
1Cx500+0 TC (H) returns 500
Cable by others returns 10
2Cx16 MC TCKX (TC)returns 16
3Cx1/0+#6G MC returns 1/0
3Cx350+#2G MC (M) returns 350
3Cx500+#2G MC (P) returns 500

I am open to other ways of accomplishing this.

thanks
 
Just use a series of standard if statements where you exit the sub when you get inside a true condition...

Make sense?

ken
 
Actually, how many different values could there be? Could you place them in a table?

???
ken
 
I tried the iif statement in query as shown in an earlier post and it was getting too complicated and so seeking an easier way - maybe in VBA to layout the IIF statement in a more manageable manner (I don't know how).

So far I believe there are about 10 variations. Can I create a table? - Well the data is of course part of another table, but Yes I could create a table of all of the unique forms of the string. I am not sure what you are thinking but this sounds good, if I get new forms I could add them to the table and add a new rule...


Thanks
 
Yeah the fuzzy part was if you had to dig out the value out of the string. That looked like it would be hard logic to figure out...

The table method could be used with a dlookup and get you away from having to do any hard coding!

ken
 
Breaking Down IIF()

If you are getting lost in a giant IIF() statement, and you want to stick to keeping this stuff in the query, here’s how I do it sometimes, since I’m not the best with VBA.

Simply break your IIF’s down into separate fields, then use these fields in your last IIF() statement.

In the query it looks like you have a few different groups of cables. Or at least you can use the similarities in the string values to break it down. You can group them however you want to, here’s an example:

1Cx: IIF(Left([cable type], 3) = “1Cx”, IIF(Mid([cable type], 4, 1) = “5”, “500”, “4/0”))

3Cx: IIF(Left([cable type],1) = “3”, IIF(Mid([cable type], 4, 1) = “1”, “1/0”, IIF(Mid([cable type], 4, 1) = “3”, “350”, “500”)))

2Cx: IIF(Left([cable type], 1) = “2”, “16” IIF(Mid([cable type], 2,1) = “2”, “14”, “No Match”))

Now that I have grouped most of the cable type’s, I only have one remaining cable type (“Cable By Others”) from the ones you listed. My last field will be written like this:

CableValues: IIF(Left([cable type], 2) = “Ca”, “10”, IIF(Left[cable type], 1) = “3”, [3Cx], IIF(Left([cable type], 2) = “1C”, [1Cx], [2Cx])))

Make sure this [CableValues] field comes after the other 3 grouped fields in the query (i.e. to the right of them in the GUI).

This should simplify the IIF() statement to a more manageable status. That Code Mile-O-Phile gave is probably better… But this should work if you have issues with VBA.

Also Ken’s idea of a table would probably work very well. You can make a table and link it to your query with all the return values in another field…

Anyway…hope this can help you. GL!

Gary
 
Post a sample database with an example of the data you have and the query you've done thus far.
 
Thanks all,

I think the table idea is great. I only have about 10 cable types, but several sizes of each, but in total maybe only about 40 different options. It will also tie to my cable reel list, in fact I will just add the look up fields to the reel table.

Thanks for a simple solution to what I seem to have over complicated.

Steve
 

Users who are viewing this thread

Back
Top Bottom