By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

conditional syntax for a combo box displaying concatenated fields

P: 59
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.
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([SiteNmAlt]),[SiteNm]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro],IIf(IsNull([CmplxNm]),[SiteNm]+" / "+[SiteNmAlt]+", "+[SiteAddr1]+", "+[SiteBoro],[SiteNm]+" / "+[SiteNmAlt]+" of "+[CmplxNm]+", "+[SiteAddr1]+", "+[SiteBoro]))
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. =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])))
  2.  
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
Aug 13 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,616
Hello Bridget, just subscribing. I will check in later, with hopefully a Reply.
Aug 13 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 13 '08 #3

ADezii
Expert 5K+
P: 8,616
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
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
    2. 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
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcatString(varSiteNm, varSiteNmAlt, varComplxNM, varSiteAddr1, varSiteBoro)
    2. If IsNull(varSiteNmAlt) And IsNull(varComplxNM) Then
    3.   fConcatString = varSiteNm & " - " & varSiteAddr1 & " - " & varSiteBoro
    4. ElseIf IsNull(varSiteNmAlt) Then
    5.   fConcatString = varSiteNm & " - " & varComplxNM & " - " & varSiteAddr1 & " - " & varSiteBoro
    6. ElseIf IsNull(varComplxNM) Then
    7.   fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varSiteAddr1 & " - " & varSiteBoro
    8. Else    'both Optional Fields have values
    9.   fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varComplxNM & " - " & _
    10.                   varSiteAddr1 & " - " & varSiteBoro
    11. End If
    12. End Function
Aug 13 '08 #4

P: 59
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
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
    2. 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
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcatString(varSiteNm, varSiteNmAlt, varComplxNM, varSiteAddr1, varSiteBoro)
    2. If IsNull(varSiteNmAlt) And IsNull(varComplxNM) Then
    3.   fConcatString = varSiteNm & " - " & varSiteAddr1 & " - " & varSiteBoro
    4. ElseIf IsNull(varSiteNmAlt) Then
    5.   fConcatString = varSiteNm & " - " & varComplxNM & " - " & varSiteAddr1 & " - " & varSiteBoro
    6. ElseIf IsNull(varComplxNM) Then
    7.   fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varSiteAddr1 & " - " & varSiteBoro
    8. Else    'both Optional Fields have values
    9.   fConcatString = varSiteNm & " - " & varSiteNmAlt & " - " & varComplxNM & " - " & _
    10.                   varSiteAddr1 & " - " & varSiteBoro
    11. End If
    12. 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
Aug 14 '08 #5

P: 59
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qrySitesAll.SiteID, fConcatString([SiteNm],[SiteNmAlt],[CmplxNm],[SiteAddr1],[SiteBoro]) AS ConcatStr, qrySitesAll.SiteNm, qrySitesAll.SiteNmAlt, qrySitesAll.CmplxNm, qrySitesAll.SiteAddr1, qrySitesAll.SiteBoro
  2. FROM qrySitesAll;
  3.  
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...

Expand|Select|Wrap|Line Numbers
  1. Public Function fConcatString(SiteNm As String, SiteNmAlt As String, CmplxNm As String, SiteAddr1 As String, SiteBoro As String)
  2. If IsNull(SiteNmAlt) And IsNull(CmplxNm) Then
  3.   fConcatString = SiteNm & " - " & SiteAddr1 & " - " & SiteBoro
  4. ElseIf IsNull(SiteNmAlt) Then
  5.   fConcatString = SiteNm & " - " & CmplxNm & " - " & SiteAddr1 & " - " & SiteBoro
  6. ElseIf IsNull(CmplxNm) Then
  7.   fConcatString = SiteNm & " - " & SiteNmAlt & " - " & SiteAddr1 & " - " & SiteBoro
  8. Else    'both Optional Fields have values
  9.   fConcatString = SiteNm & " - " & SiteNmAlt & " - " & CmplxNm & " - " & _
  10.                   SiteAddr1 & " - " & SiteBoro
  11. End If
  12. End Function
  13.  
Any idea why i might be getting this error message?
Thanks again, Bridget
Aug 14 '08 #6

ADezii
Expert 5K+
P: 8,616
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qrySitesAll.SiteID, fConcatString([SiteNm],[SiteNmAlt],[CmplxNm],[SiteAddr1],[SiteBoro]) AS ConcatStr, qrySitesAll.SiteNm, qrySitesAll.SiteNmAlt, qrySitesAll.CmplxNm, qrySitesAll.SiteAddr1, qrySitesAll.SiteBoro
  2. FROM qrySitesAll;
  3.  
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...

Expand|Select|Wrap|Line Numbers
  1. Public Function fConcatString(SiteNm As String, SiteNmAlt As String, CmplxNm As String, SiteAddr1 As String, SiteBoro As String)
  2. If IsNull(SiteNmAlt) And IsNull(CmplxNm) Then
  3.   fConcatString = SiteNm & " - " & SiteAddr1 & " - " & SiteBoro
  4. ElseIf IsNull(SiteNmAlt) Then
  5.   fConcatString = SiteNm & " - " & CmplxNm & " - " & SiteAddr1 & " - " & SiteBoro
  6. ElseIf IsNull(CmplxNm) Then
  7.   fConcatString = SiteNm & " - " & SiteNmAlt & " - " & SiteAddr1 & " - " & SiteBoro
  8. Else    'both Optional Fields have values
  9.   fConcatString = SiteNm & " - " & SiteNmAlt & " - " & CmplxNm & " - " & _
  10.                   SiteAddr1 & " - " & SiteBoro
  11. End If
  12. End Function
  13.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
  2. FROM tblSites;
Aug 14 '08 #7

P: 59
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
  2. 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
Aug 19 '08 #8

ADezii
Expert 5K+
P: 8,616
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:
Expand|Select|Wrap|Line Numbers
  1. Forms - frm...
  2. Reports - rpt...
  3. Modules - mdl...
  4. Macros - mcr...
  5. Tables - tbl...
  6. Queries - qry...
  7. Functions - fFunctionName
  8. Sub Routines - Just the Name
  9. Command Buttons - cmd...
  10. Combo Boxes - cbo...
  11. List Boxes - lst...
  12. Text Boxes - txt...
  13. Labels - lbl...
  14. I think you get the idea...
Aug 19 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.