Access 2000 query help

[Gale Coleman]

Hello all,

I am trying to design a query that will give me a search field. I want to
be able to search a number of fields in a database for a certain word or
words. I am having a problem with the query working properly when there is
nothing in the field. The query comes up blank.

Is there something that I can code so it will give me the results even if
the fields are blank?

Following is the SQL:

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

 

[Tonín]

I suggest you should use "&" instead of "+" to concatenate the fields

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;

Tonín

 

[Gale Coleman]

I did that and it works great.

Thank you.

Gale