joining fields - exclude empty string

[kristinac]

I am combining 4 fields in my table into one. The third field is often either Null or an empty string. I know to exclude the Null values you use the + instead of &.

SELECT Name & ' ' & Type & ' (' + SRNumber + ')' & ' ' & City AS FullName

That works great when SRNumber is Null. But many times it is blank. If so, I get the parentheses - it does not exclude the field. Is there a way to exclude SRNumber if it contains an empty string?

Thanks in advance...

 

[DanChard]

How about this?

select name & ' ' & type & switch(srnumber,len(nz(srnumber,"&quot=0,' ',len(nz(srnumber,"&quot)>0,' (' & srnumber & ')') & ' ' & city as fullname

Good luck,
Dan

 

[dhookom]

Try:
SELECT Name & ' ' & Type & IIf(Len(SRNumber & "&quot>0,' (' & SRNumber & ')', '') & ' ' & City AS FullName

Duane
MS Access MVP

 

[kristinac]

Okay. This is the error I get when I use your suggestion:

Wrong number of arguments used with function in query expression 'Name & ' ' & Type & switch(SRNumber,len(nz(SRNumber,&quot=0,' ',len(nz(SRNumber,&quot)>0,' (' & SRNumber & ')') & ' ' & City'.

I need to look up the switch function.

 

[kristinac]

This is the error I get with the second one:

Syntax error in string in query expression 'Name & ' ' & Type & IIf(Len(SRNumber & &quot>0,' (' & SRNumber & ')', '') & ' ' & City AS FullName FROM Streets ORDER BY Name, Type, SRNumber, City'.

You can use SQL functions if the database is in Access can't you?

Thank you both for your suggestions.

 

[kristinac]

Okay I was trying to run a query within access itself using the IIF function.

SELECT Name, SRNumber, IIF(SRNumber>0, "no","yes&quot AS FullName FROM Streets

Anywhere SRNumber was NOT >0, the field was yes. Anywhere SRNumber was > 0, the field said #ERROR.

Any ideas?

 

[kristinac]

Okay I don't know what I screwed up the first time Duane. Yours is working great. Thank you!

 

[dhookom]

Glad to hear it is working for you. One other point/suggestion: name is a very poor name for a field since every object in Access has a name property. If you place a text box in any report
=[Name]
this will display the name of the report.

Duane
MS Access MVP