Concatenation

[lantoni]

I have a similar issue to https://www.accessforums.net/access/...lved-8444.html; I need to combine six fields instead of two with Null fields involved. I'm a novice, using Access 2010. I've tried some variations based on this thread but evidently I'm missing something on the syntax.


My six fields are:
[Residence in]
[Fellowship in]
[Special Skills in]
[Experience in]
[Contractual Arrangements]
[Other Stipulations]

I'm definitely missing something, because when I run the query it keeps showing ";" between the fields when they are NULL.

This is what I've tried (please don't laugh):

Full_Stips: IIf([Residency in] Is Null,IIf([Fellowship in] Is Null,"",[Fellowship in]),IIf([Fellowship in] Is Null,[Residency in],[Residency in] & "; " & [Fellowship in])) & ";" & (IIf([Special Skills in] Is Null,IIf([Experience in] Is Null,"",[Experience in]),IIf([Experience in] Is Null,[Special Skills in],[Special Skills in] & "; " & [Experience in]))) & ";" & (IIf([Contractual Arrangements] Is Null,IIf([Other Stipulations] Is Null,"",[Other Stipulations]),IIf([Other Stipulations] Is Null,[Contractual Arrangements],[Contractual Arrangements] & "; " & [Other Stipulations])))

I really hope you can help me out.

Thanks,
Leila

[June7]

Six fields makes for a very long and complicated expression. Don't think I would even try.

I suspect your data structure is not normalized.

[lantoni]

I need to display those in a report, all together, under a single label "Stipulations". How do you suggest I do that without ending up with "blank/empty" lines/spaces when one of the fields is NULL?

This is driving me nuts!!

[June7]

Non-normalized data structure can have that influence.

The issue with expression is that whether or not ; is required depends on the field and each field that follows.

If the data were normalized, it would be a simple query.

A UNION query could rearrange the data into normalized structure. The null records could be filtered out. Then that query could be joined into the report RecordSource or be used in a subreport.

SELECT ID, "Res" As StipType, [Residence in] AS Stipulation FROM tablename WHERE [Residence in] Not Is Null
UNION SELECT ID, "Fel", [Fellowship in] FROM tablename WHERE [Fellowship in] Not Is Null
UNION SELECT ID, "SS", [Special Skills in] FROM tablename WHERE [Special Skills in] Not Is Null
UNION SELECT ID, "Exp", [Experience in] FROM tablename WHERE [Experience in] Not Is Null
UNION SELECT ID, "CA", [Contractual Arrangements] FROM tablename WHERE [Contractual Arrangements] Not Is Null
UNION SELECT ID, "OS", [Other Stipulations] FROM tablename WHERE [Other Stipulations] Not Is Null;

UNION query must be typed or copy/paste into SQL View of query builder. Limit of 50 SELECT lines.

I am guessing there is a unique ID field of some name. Include whatever fields are needed such as customer ID or contract ID, etc.


Otherwise, need VBA function procedure.

[MarvinM]

Leila,

I have a similar situation. I read values from a data input screen and then string them together into a SQL statement. I have over twenty controls to accept data and there are always nulls present. So I first create variables for each and determine if there is a null in the control or not, like this:

    strReg = Nz([cboRegion].Value, "")
    strSvc = Nz([cboSvc].Value, "")
    strGeo = Nz([cboGeo].Value, "")
    strFac = Nz([cboFac].Value, "")
    strDpt = Nz([txtDept].Value, "")

So let me try to put it in your terms.

Dim strRes as String
Dim strFel as String
Dim strSpe as String
Dim strExp as String
Dim strCon as String
Dim strOth as String
Dim strStip as String

strRes = Nz([Residence in].Value, "")
strFel = Nz([Fellowship in].Value, "")
strSpe = Nz([Special Skills in].Value, "")
strExp = Nz([Experience in].Value, "")
strCon = Nz([Contractual Arrangements].Value, "")
strOth = Nz([Other Stipulations].Value, "")

strStip = Iif(Len(strRes)>0, strRes, "")
strStip = Iif(Len(strStip)>0 AND Len(strFel)>0, strStip & "; " & strFel, strFel)
strStip = Iif(Len(strStip)>0 AND Len(strSpe)>0, strStip & "; " & strSpe, strSpe)
strStip = Iif(Len(strStip)>0 AND Len(strExp)>0, strStip & "; " & strExp, strExp)
strStip = Iif(Len(strStip)>0 AND Len(strCon)>0, strStip & "; " & strCon, strCon)
strStip = Iif(Len(strStip)>0 AND Len(strOth)>0, strStip & "; " & strOth, strOth)

So now you have a string variable, strStip, with the string of concatenated values, separated by semicolons. I hope this gets your project moving.
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
-------------------------------------------------------------------------------------------------------------------
If this post has helped you, please click on the little sheriff badge in the lower left corner. Thanks!
-------------------------------------------------------------------------------------------------------------------

[orange]

lantoni,

As June7 suggested, it appears your data is not normalized. If that is true, you will be woking against yourself with most data entry and retrieval. Access is a relational database and is built on certain concepts and principles to ease data entry, validation, retrieval and integrity. Perhaps you could tell us more about your database and what it is about. Once we know WHAT you're doing, I'm sure more responses/suggestions/comments will follow.

The response you have gotten are trying to deal with your specific question, but not in the context of your overall database.

Anyway just my $.02 for consideration.

[lantoni]

As I mentioned, I'm a novice. So here goes with my first stupid question: what do you mean by normalized data? I've never heard that.

To answer Orange’s question, I use this database to log and report physicians data related to medical staff membership stipulations. Those are considered legal documents as well as departmental working reports. In one of my reports I need to display all those fields under "stipulations". All my tables have unique IDs and are connected via relationships. Everything works as I need except for this report. I haven't had the chance to try the suggestions above yet, but I sure will.

Please keep suggestions coming, I hope the extra information posted helps with this puzzle.

[June7]

Bing: database normalization

http://support.microsoft.com/kb/283878

[lantoni]

June7, thanks for the link. I've read the article and based on that, I can say that my database is not in bad shape. I'm going to try your suggestion in a couple minutes. I'll report back on the results.

[orange]

lantoni,

Watch these free videos


http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck.

[lantoni]

June7, I've tried the UNION query as you mentioned but I keep getting a "syntax error in FROM clause". Below is what I've used:

SELECT ID, "Res" As StipType, [Residence in] AS Stipulation FROM Req&Stips WHERE [Residence in] Not Is Null
UNION SELECT ID, "Fel", [Fellowship in] FROM Req&Stips WHERE [Fellowship in] Not Is Null
UNION SELECT ID, "SS", [Special Skills in] FROM Req&Stips WHERE [Special Skills in] Not Is Null
UNION SELECT ID, "Exp", [Experience in] FROM Req&Stips WHERE [Experience in] Not Is Null
UNION SELECT ID, "CA", [Contractual Arrangements] FROM Req&Stips WHERE [Contractual Arrangements] Not Is Null
UNION SELECT ID, "OS", [Other Stipulations] FROM Req&Stips WHERE [Other Stipulations] Not Is Null
UNION SELECT ID, "Req", [Requirements] FROM Req&Stips WHERE [Requirements] Not Is Null;



Req&Stips = my table name
Requirements = unique ID field

What am I doing wrong? This is my first time using sequel.

[lantoni]

Orange, thank you for posting the free videos. I welcome all help I can get :-)

[June7]

Special character in the table name. Enclose in brackets: [Req&Stips]

Advise avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

[lantoni]

I've added the brackets to encase the table name and now I get the following error:

Syntax error in query expression '[Residence in] NOT Is Null'.

I'm confused. I've been goggling "sequel syntax" to try and learn a little about it.

[orange]

Please post the sql you used that received the error.

For SQL information and examples/tutorials