conditional syntax for a combo box displaying concatenated fields

[banderson]

Hello,
I have a combo box in which I want to display multiple fields by concatenating the fields together. If one of those concatenated fields is Null, then the combo box does not show anything. To rectify this I have created IIf statements to check if certain fields are Null and remove those fields from the concatenation. The problem I am having is how to check if multiple fields are Null. I think I need to use AND in my code, but cannot figure out how to make it work.

My combo box is called cboSite, based on a query called qrySitesAll.
The bound column is an autonumber primary key field called SiteID.
The fields I would like to concatenate into the second column of the combo box (that will be displayed in the combo box) are:
SiteNm
SiteNmAlt (optional)
CmplxNm (optional)
SiteAddr1
SiteBoro

The code below works - it checks if SiteNmAlt is Null and, if Null, excludes SiteNmAlt from the concatenation, then it checks if CmplxNm is Null and, if Null, excludes CmplxNm from the concatenation.

IIf(IsNull([SiteNmAlt]),[SiteNm]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro],IIf(IsNull([CmplxNm]),[SiteNm]+" / "+[SiteNmAlt]+", "+[SiteAddr1]+", "+[SiteBoro],[SiteNm]+" / "+[SiteNmAlt]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro]))
 

I tried to use the following code to check if both SiteNmAlt and CmplxNm are Null and, if Null, exclude both from the concatenation, but it gave me the error message that I have invalid syntax. I have played with the syntax, but am coming up with no solution.

=IIf(AND(IsNull([SiteNmAlt]),IsNull([CmplxNm])),[SiteNm]+", "+[SiteAddr1]+", "+[SiteBoro]IIf(IsNull([SiteNmAlt]),[SiteNm]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro],IIf(IsNull([CmplxNm]),[SiteNm]+" / "+[SiteNmAlt]+", "+[SiteAddr1]+", "+[SiteBoro],[SiteNm]+" / "+[SiteNmAlt]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro])))
 

Does anyone have a suggestion as to how I could improve this syntax, or, alternately, a suggestion for an expression that would allow me to check the fields in each record and exclude null fields from the concatenation?

Thanks in advance,
Bridget

[ADezii]

Hello Bridget, just subscribing. I will check in later, with hopefully a Reply.

[missinglinq]

These multiple, combined IIFs always give me a headache, and are really an evil construct to try to figure out, when they go bad, but the first thing you need to do is to take out the plus sign (+) that you're using for your concatenation and use the ampersand (&) instead. While the plus sign works some of the times, it frequently confuses the Access Gnomes and causes erratic behavior.

Linq ;0)>

[ADezii]

Hello Bridget, back again! It seems as though you have 4 possible conditions you have to worry about, and I totally agree with Linq about multiple IIfs, they are truly a nightmare. Back to the conditions:

1. Both Optional Fields are Null.
2. Both Optional Fields are Not Null.
3. Optional Field 1 Is Null, 2 is Not Null.
4. Optional Field 2 Is Null, 1 is Not Null.

In my opinion, this kind of logic should be handled via a Function Call for a Calculated Field (2nd Column in Combo Box), in a Query (qrySitesAll). Assuming your Table Name is tblSites, I've created a fully operational Query and Function for you. All Arguments have been defined as Variant since I don't know their true Data Types, and the Optional ones must be defined as such because of the possibility of Nulls. I'm rather pressed for time at the moment, so I'll post the SQL and Function Code. There may be a better, more efficient solution, so please do not accept my Reply as your only option. Let's also wait for Linq's opinion, it is always welcomed and valued. Any questions, please do not hesitate to ask.

P.S. - Change the actual syntax within the Function to suit your specific needs.

1. qrySitesAll
   

   SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
   FROM tblSites;

2. Function (fConcatString) Definiition. This Functions covers all 4 possibilities for the 2 Optional Fields as discussed previously and returns the proper Concatenated String Value
   

   Public Function fConcatString(varSiteNm, varSiteNmAlt, varComplxNM, varSiteAddr1, varSiteBoro)
   If IsNull(varSiteNmAlt) And IsNull(varComplxNM) Then
     fConcatString = varSiteNm & " - " & varSiteAddr1 & " - " & varSiteBoro
   ElseIf IsNull(varSiteNmAlt) Then
     fConcatString = varSiteNm & " - " & varComplxNM & " - " & varSiteAddr1 & " - " & varSiteBoro
   ElseIf IsNull(varComplxNM) Then
     fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varSiteAddr1 & " - " & varSiteBoro
   Else    'both Optional Fields have values
     fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varComplxNM & " - " & _
                     varSiteAddr1 & " - " & varSiteBoro
   End If
   End Function

[banderson]

Hello Bridget, back again! It seems as though you have 4 possible conditions you have to worry about, and I totally agree with Linq about multiple IIfs, they are truly a nightmare. Back to the conditions:

1. Both Optional Fields are Null.
2. Both Optional Fields are Not Null.
3. Optional Field 1 Is Null, 2 is Not Null.
4. Optional Field 2 Is Null, 1 is Not Null.

In my opinion, this kind of logic should be handled via a Function Call for a Calculated Field (2nd Column in Combo Box), in a Query (qrySitesAll). Assuming your Table Name is tblSites, I've created a fully operational Query and Function for you. All Arguments have been defined as Variant since I don't know their true Data Types, and the Optional ones must be defined as such because of the possibility of Nulls. I'm rather pressed for time at the moment, so I'll post the SQL and Function Code. There may be a better, more efficient solution, so please do not accept my Reply as your only option. Let's also wait for Linq's opinion, it is always welcomed and valued. Any questions, please do not hesitate to ask.

P.S. - Change the actual syntax within the Function to suit your specific needs.

1. qrySitesAll
   

   SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
   FROM tblSites;

2. Function (fConcatString) Definiition. This Functions covers all 4 possibilities for the 2 Optional Fields as discussed previously and returns the proper Concatenated String Value
   

   Public Function fConcatString(varSiteNm, varSiteNmAlt, varComplxNM, varSiteAddr1, varSiteBoro)
   If IsNull(varSiteNmAlt) And IsNull(varComplxNM) Then
     fConcatString = varSiteNm & " - " & varSiteAddr1 & " - " & varSiteBoro
   ElseIf IsNull(varSiteNmAlt) Then
     fConcatString = varSiteNm & " - " & varComplxNM & " - " & varSiteAddr1 & " - " & varSiteBoro
   ElseIf IsNull(varComplxNM) Then
     fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varSiteAddr1 & " - " & varSiteBoro
   Else    'both Optional Fields have values
     fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varComplxNM & " - " & _
                     varSiteAddr1 & " - " & varSiteBoro
   End If
   End Function

Hi Linq and ADezii - So glad I'm not the only one tearing my hair out with these multiple Iif statements! Great suggestion on the ampersand and thanks so much for the code with the function call! I will get to work trying it out and post back with problems and the final solution.
Bridget

[banderson]

Hi Linq and ADezii - So glad I'm not the only one tearing my hair out with these multiple Iif statements! Great suggestion on the ampersand and thanks so much for the code with the function call! I will get to work trying it out and post back with problems and the final solution.
Bridget

Well, I tried the code and seem to be having a problem getting the query to read the function. When I click on the combo box drop down I get the error message:
"Undefined function ‘fConcatString’ in expression."

I have to admit this is my first time using a pubic function, so it is possible I am doing something wrong, though after reading a few online tutorials on creating public functions, I cannot seem to figure it out. The query code (based on your code) is:

SELECT qrySitesAll.SiteID, fConcatString([SiteNm],[SiteNmAlt],[CmplxNm],[SiteAddr1],[SiteBoro]) AS ConcatStr, qrySitesAll.SiteNm, qrySitesAll.SiteNmAlt, qrySitesAll.CmplxNm, qrySitesAll.SiteAddr1, qrySitesAll.SiteBoro
FROM qrySitesAll;
 

To create the public function, I added a new module and put in the following code (based on your code). Since all of the fields are string, I switched everything to string thinking it might, for some reason, help fix the error, but it did not work...

Public Function fConcatString(SiteNm As String, SiteNmAlt As String, CmplxNm As String, SiteAddr1 As String, SiteBoro As String)
If IsNull(SiteNmAlt) And IsNull(CmplxNm) Then
  fConcatString = SiteNm & " - " & SiteAddr1 & " - " & SiteBoro
ElseIf IsNull(SiteNmAlt) Then
  fConcatString = SiteNm & " - " & CmplxNm & " - " & SiteAddr1 & " - " & SiteBoro
ElseIf IsNull(CmplxNm) Then
  fConcatString = SiteNm & " - " & SiteNmAlt & " - " & SiteAddr1 & " - " & SiteBoro
Else    'both Optional Fields have values
  fConcatString = SiteNm & " - " & SiteNmAlt & " - " & CmplxNm & " - " & _
                  SiteAddr1 & " - " & SiteBoro
End If
End Function
 

Any idea why i might be getting this error message?
Thanks again, Bridget

[ADezii]

Well, I tried the code and seem to be having a problem getting the query to read the function. When I click on the combo box drop down I get the error message:
"Undefined function ‘fConcatString’ in expression."

I have to admit this is my first time using a pubic function, so it is possible I am doing something wrong, though after reading a few online tutorials on creating public functions, I cannot seem to figure it out. The query code (based on your code) is:

SELECT qrySitesAll.SiteID, fConcatString([SiteNm],[SiteNmAlt],[CmplxNm],[SiteAddr1],[SiteBoro]) AS ConcatStr, qrySitesAll.SiteNm, qrySitesAll.SiteNmAlt, qrySitesAll.CmplxNm, qrySitesAll.SiteAddr1, qrySitesAll.SiteBoro
FROM qrySitesAll;
 

To create the public function, I added a new module and put in the following code (based on your code). Since all of the fields are string, I switched everything to string thinking it might, for some reason, help fix the error, but it did not work...

Public Function fConcatString(SiteNm As String, SiteNmAlt As String, CmplxNm As String, SiteAddr1 As String, SiteBoro As String)
If IsNull(SiteNmAlt) And IsNull(CmplxNm) Then
  fConcatString = SiteNm & " - " & SiteAddr1 & " - " & SiteBoro
ElseIf IsNull(SiteNmAlt) Then
  fConcatString = SiteNm & " - " & CmplxNm & " - " & SiteAddr1 & " - " & SiteBoro
ElseIf IsNull(CmplxNm) Then
  fConcatString = SiteNm & " - " & SiteNmAlt & " - " & SiteAddr1 & " - " & SiteBoro
Else    'both Optional Fields have values
  fConcatString = SiteNm & " - " & SiteNmAlt & " - " & CmplxNm & " - " & _
                  SiteAddr1 & " - " & SiteBoro
End If
End Function
 

Any idea why i might be getting this error message?
Thanks again, Bridget

qrySitesAll is the Name of the actual Query itself, I assume. The qrySites should be based on tblSites or whatever Name your Table is, as the SQL clearly indicates:

SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
FROM tblSites;

[banderson]

qrySitesAll is the Name of the actual Query itself, I assume. The qrySites should be based on tblSites or whatever Name your Table is, as the SQL clearly indicates:

SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
FROM tblSites;

Hello ADezii,
Thank you for your response. I tried basing the query on the table and still got the error Undefined Function "" in Expression. After looking at a number of manuals & listserves, I figured out that the problem was not in the query, but rather that I had named the Module the same name as the function fConcatString! Apparently the module and function w/in the module cannot have the same name. After changing the module name, it worked.

Thanks again for you help and code.
Bridget

[ADezii]

Hello ADezii,
Thank you for your response. I tried basing the query on the table and still got the error Undefined Function "" in Expression. After looking at a number of manuals & listserves, I figured out that the problem was not in the query, but rather that I had named the Module the same name as the function fConcatString! Apparently the module and function w/in the module cannot have the same name. After changing the module name, it worked.

Thanks again for you help and code.
Bridget

Hello Bridget, glad you figured it all out! Here is a prime example why it is imperative that you maintain some kind of consistent and unique naming convention for all your Objects. As a matter of reference, here are some of the Prefixes I use in naming various Objects:

Forms - frm...
Reports - rpt...
Modules - mdl...
Macros - mcr...
Tables - tbl...
Queries - qry...
Functions - fFunctionName
Sub Routines - Just the Name
Command Buttons - cmd...
Combo Boxes - cbo...
List Boxes - lst...
Text Boxes - txt...
Labels - lbl...
I think you get the idea...