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
8 2678 ADezii 8,834
Recognized Expert Expert
Hello Bridget, just subscribing. I will check in later, with hopefully a Reply.
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 8,834
Recognized Expert Expert
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: - Both Optional Fields are Null.
- Both Optional Fields are Not Null.
- Optional Field 1 Is Null, 2 is Not Null.
- 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. - qrySitesAll
- SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
-
FROM tblSites;
- 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
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:- Both Optional Fields are Null.
- Both Optional Fields are Not Null.
- Optional Field 1 Is Null, 2 is Not Null.
- 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.- qrySitesAll
- SELECT tblSites.SiteID, tblSites.SiteNM, tblSites.SiteNMAlt, tblSites.ComplxNM, tblSites.SiteAddr1, tblSites.SiteBoro, fConcatString([SiteNM],[SiteNmAlt],[ComplxNM],[SiteAddr1],[SiteBoro]) AS ConcatStr
-
FROM tblSites;
- 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
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 8,834
Recognized Expert Expert
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;
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 8,834
Recognized Expert Expert
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...
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: B Love |
last post by:
I would like to display a drop-down list on a form conditional on the value
of another field on the same form. I am not sure if this is good form or
not. I am using Access97, but have access to 2000 also. The specific
scenario is this: I have 2 fields on a form. One is called "Manufacturer."
The other is called "ProductLine." I would like to display specific product
lines of the selected Manufacturer. Now, here's the bad news: my SQL and VB...
|
by: CSDunn |
last post by:
Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.
I have run into a problem when either the 'FirstName' or 'LastName' contains...
|
by: Megan |
last post by:
Can you write conditional VBA code that affects only one or two
records on a continuous subform?
I have a form with a subform on it. The parent/ child field that links
the forms is CaseID. The main form has personal info on it. The
subform deals with each case's Issues, Decisions, and Notices.
Issues, Decisions, and Notices are all combo boxes. Not all Issues
have Notices. Issues 25-50 have Notices. Issues 1-24 and 51-100 don't
have...
|
by: Elena |
last post by:
Hi,
I am filling in a combobox. I would like to concatenate two fields into the
data combo box and display "last name, first name"
I tried to displaymember = "employee_last_name" & ", " &
"employee_last_name", but it did not like that.
I can fill the combo box with either the first or the last, but I cannot
manage to concatenate it.
|
by: Erich Kohl |
last post by:
Okay, here's the deal:
Let's say a form is based on a table.
This form has Field1 (PrimeKey), Field2, Field3, etc.
This form also has a subform which shows related records in another
table. The main fields on the main form (Field1, Field2, etc.) are
simply there for show, not for editing.
| |
by: Jeremy Wallace |
last post by:
Folks,
Here's a write-up I did for our developer wiki. I don't know if the
whole rest of the world has already figured out how to do this, but I
hadn't ever seen it implemented, and had spent a lot of time trying to
figure it out, over the years. It finally dawned on me a couple of
weeks ago how to do this.
A couple of notes:
1) This is written for a different audience than CDMA; it's written for
|
by: Exick |
last post by:
This is more of a minor annoyance/curiosity than a real problem, but I'm wondering if anyone here can provide some answers.
I have a form bound to a table with lots of controls on it that are bound to fields of said table. I also have a combo box on the form that is unbound. I'm using it purely as a search mechanism. Anyway, the combo box dropdown is populated with data from a SQL query. There are 5 fields total, only the first 2 are...
|
by: biganthony via AccessMonster.com |
last post by:
Hi,
I decided to install Office 2003 Service Pack 3 on my home computer to test
(in full knowledge that there may be some issues with it). After installation,
I have noticed that with a small database I wrote for home, the combo boxes
and listboxes no longer display the bound column. For example, on a form I
have a combo box based on a table called 'names'. The two columns in the
combo box are ID and Surname. The combo box and list box...
|
by: canadianinmd |
last post by:
This is basically my problem I have data comming from a database and storing fields in a variable ie
Name = ORs("Name")
Address= ORs("Address")
DisplayVar = Name: Address
My goal is to display these values in a combo box as a concatenated variable.
However, by example..here is my problem
when I run a for loop to add DisplayVar to a combo box I get values exactly like this:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |