Nested iif for query expression (1 Viewer)

[Holly_Associated]

Hi All,

I am trying to use a six nested iif statements to evaluate to one line of text from three fields.

I have made a table of possible outcomes and written each condition and truepart from it, with a seventh line being the falsepart on the end. I use this with the expression builder to make the full expression, just copying and pasting and inserting the commas where needed. It usually works for me!

I have attached a tester db, and a shot of my table for reference. The thing is, with my tester data in the table, lines 4 and 6 do not evaluate properly but the rest do.

I've tried rearranging the order of the "is null" and "is not null", the order the fields appear in and the order the "and" appear in. I'm sure I've double and triple checked my parentheses but I've been troubleshooting this for over an hour now and its time to call in the back up.

If anyone can see where I've gone wrong I'd much appreciate you pointing it out! Alternatively, if you know of a more efficient way for me to achieve the same result using VBA or SQL and don't mind explaining it, I'd love to hear it too!

* Crosses fingers and hopes its not a blooming comma or bracket in the wrong place *

Thanks,
Holly

 

[pr2-eugin]

Okay Holly, what is that you are after. Your Screen shot involves three fields, your DB involves 5. Show examples of the Test DB, what result you want.

 

[Holly_Associated]

Hi Paul,

This expression will be used as the first line of an address label but my users don't always fill in all fields.
I think it is called concatenating the fields? I call it joining!
I've used the condition of the "Title" being null or not to assume if it has contents, then the firstname and lastname will have contents too. Three fields was enough for me to make the plan, I don't want to use all five. The Company and Department fields could be null or not too.
From the plan, if Company and Title are filled in, then the fields Company, Title, FirstName and LastName are trimmed with a comma between the Company and full name. This continues down the table until I get to a situation where all three fields have contents and so all five fields are trimmed and comma'd in the appropriate place.

Hope this is what you meant!

 

[pr2-eugin]

See if this is helpful ! I just created a function, that will take in the fields as argument, played around with the IsNull function and got the result.

 

[Holly_Associated]

Hi Paul,

It works perfectly of course. Thank you so much!

I can read through most of it, but the last two "paragraphs" are not familiar to me. I've written some notes trying to understand what you've created but if you could explain a bit more it would really help. Trying to learn for myself is the ultimate goal!

Public Function getMeRight(compVar As Variant, titleVar As Variant, _
                           FNameVar As Variant, LNameVar As Variant, deptVar As Variant) As String
                         [COLOR=green] 'Public Function returns a value of the type String as a variable with the name getMeRight
                           'This variable can be used elsewhere in the database by referencing the name getMeRight
                           'I don't see the reference to my table and field names. How does VBA know that compVar is going to be tblAddress.Company?
                           'Is this why in the query there is the open parentheses and then the table.field names?
[/COLOR]                                                                                                           
'**********************
'Code Courtesy of
'  Paul Eugin
'**********************
     Dim nameStr As String, compStr As String, deptStr As String
    Dim tmpArr() As String, retStr As String, iCtr As Long
  [COLOR=green]  'Dim is used to define variables, "telling" VBA that you are going to use them.
    'I understand the top line. I can see the bottom line is used below but am not sure how.
[/COLOR]     
    
    nameStr = Trim("" & (" " + titleVar) & (" " + FNameVar) & (" " + LNameVar)) & "/"
    compStr = IIf(IsNull(compVar), vbNullString, "/" & compVar)
    deptStr = IIf(IsNull(deptVar), vbNullString, "/" & deptVar)
 [COLOR=green]   'Can you explain the use of "/" please? I see it is the falsepart, so if the string isn't null it returns "/" & the Var?
[/COLOR]           
    tmpArr = Split(nameStr & compStr & deptStr, "/")
    For iCtr = 0 To UBound(tmpArr)
        If Len(tmpArr(iCtr) & vbNullString) <> 0 Then retStr = retStr & tmpArr(iCtr) & ", "
    Next
    
    If Len(retStr & vbNullString) <> 0 Then retStr = Left(retStr, Len(retStr) - 2)
    getMeRight = retStr
    
  [COLOR=green]  'I'm a bit lost on these ones if you have the time to explain?
[/COLOR]   
End Function

Thank you again for your help

Holly

 

[pr2-eugin]

Okay, I will try to explain this in bits,

Public Function getMeRight(compVar As Variant, titleVar As Variant, _
                           FNameVar As Variant, LNameVar As Variant, deptVar As Variant) As String

This line of code will take in arguments that can be used by the function. I have used the names like compVar, titleVar, FNameVar, LNameVar, deptVar so it can be easily recogonized as what they refer to. So when passing to this fucntion from a query per say, you use it like.

theNewColumn : getMeRight(tblAddress.Company, tblAddress.Title, tblAddress.FirstName, tblAddress.LastName, tblAddress.Dept)

So as you see that Dim is used to declare variables, since we want a String output, we try to parse the Variants to Strings (although not necessary, just makes it a bit clean).

Dim nameStr As String, compStr As String, deptStr As String
Dim tmpArr() As String, retStr As String, iCtr As Long

The first line is just creating the Name, Company and Department. The second line is to generate the outcome, I will go through when we get using them.

nameStr = Trim("" & (" " + titleVar) & (" " + FNameVar) & (" " + LNameVar)) & "/"

The (" " + someMaybeNullValue) is called as Null propogation, so if the variable is Null the return will be Null, if not the concatenation of the things before the + will be included.

compStr = IIf(IsNull(compVar), vbNullString, "/" & compVar)
deptStr = IIf(IsNull(deptVar), vbNullString, "/" & deptVar)

So as you can see we are testing for Null values, if they are Null no worries, if not we add the company and dept with a / in front of it, this is also done at the end of the nameStr. Why? Keep reading.

The Split function will actually split the function based on the delimiter we provide in this case /

tmpArr = Split(nameStr & compStr & deptStr, "/")

So if the name was given, but no company name but have a dept, the String generated by nameStr & compStr & deptStr would be,

Mr Paul Eugin//IT

So when Split, we get.

tmpArr(0) = Mr Paul Eugin
tmpArr(1) = vbNullString
tmpArr(2) = IT

Now, we loop through this Array, to get the values we want in the format we want. Since Array's normally are ZERO bound, we start with 0 until the Upper Bound of the Array.

For iCtr = 0 To UBound(tmpArr)

We test for Strings that are not Null Strings, so the above test will fail for iCtr = 1, as we have nothing there.

If Len(tmpArr(iCtr) & vbNullString) <> 0 Then

We create a String from the tmpArr, when they are not Null String.

FiretStr = retStr & tmpArr(iCtr) & ", "

So the generated String will be,

Mr Paul Eugin, IT,

Now we only want everything before the last comma. So we use the Left fucntion.

retStr = Left(retStr, Len(retStr) - 2)

The following test, is just a safe test, if all the values passed to the function is Null.

If Len(retStr & vbNullString) <> 0

Finally we send the String

getMeRight = retStr

Does this help?

 

[Holly_Associated]

Hi Paul,

Thank you so much for taking the time to explain this. I can't express enough my appreciation! I shall be poring over this for the next few days and making some concatenations of my own. I'll mark this thread as solved

Thank you again,
Holly

 

[pr2-eugin]

You are most welcome ! Glad to be of help.

PS: Can you have a look into this thread Calling all members in UK?
http://www.access-programmers.co.uk/forums/showthread.php?t=261075