Problems with concatenating when text field is empty

[Gale Coleman]

Hello all,

I will attempt to explain myself. I am using Access 2000. I need to write
a query that concatenates a number of fields into a new field called
searchfield.

Everything works fine when there is something in every field, however, there
is one table I am using that stores the names of documents. Some records
have 10 documents, some have 5 documents, so in some cases there are empty
fields. I find that when there are empty fields, the concatenation does not
complete and my new "searchfield" is empty.

Is there a way to write my query so it will ignore the fields that have
nothing in them and concatenate anyway? Following is the sql to my query.
Some of the fields named docname* are empty.

SELECT [Choices].[ChoicesName]+' '+[ChoicesDescription]+'
'+[Documents].[DocName1]+' '+[Documents].[DocName2]+'
'+[Documents].[DocName3]+' '+[Documents].[DocName4]+'
'+[Documents].[DocName5]+' '+[Documents].[DocName6]+'
'+[Documents].[DocName7]+' '+[Documents].[DocName8]+'
'+[Documents].[DocName9]+' '+[Documents].[DocName10] AS searchfield,
Choices.ChoicesDescription, Choices.ChoicesName, CaseTypes.CaseType,
Choices.ChoicesID, Documents.DocName1, Documents.DocName2,
Documents.DocName3, Documents.DocName4, Documents.DocName5,
Documents.DocName6, Documents.DocName7, Documents.DocName8,
Documents.DocName9, Documents.DocName10, Choices.LinkedTo
FROM (Categories RIGHT JOIN (CaseTypes RIGHT JOIN Choices ON
CaseTypes.CaseTypeID = Choices.CasetypeID) ON Categories.CategoryID =
Choices.CategoryID) LEFT JOIN Documents ON Choices.ChoicesID =
Documents.ChoicesID;

Thanks,

Gale

 

[Arvin Meyer]

A "+" sign will propogate a null. An ampersand "&" will not. So:


[LastName] & ", " & [FirstName]
Meyer, Arvin
, Arvin
Meyer,
,

[LastName] + ", " + [FirstName]
Meyer, Arvin
NULL
NULL
NULL


[LastName] & (", " + [FirstName])
Meyer, Arvin
Arvin
Meyer


--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[David Phelan]

Gale,

Try using the IsNull frunction. IsNull([Documents].[DocName1],'')+'
'+IsNull([Documents].[DocName2],'') etc.

As long as there is a value or an empty string, your code should be no
problem. You may be hitting a snag if there are nulls in your data.

Dave

 

[Gale Coleman]

Thank you,

I will try both tomorrow and post back.

Gale

 

[Gale Coleman]

Using the & worked exactly how I wanted.

Thank you.

Gale