Substitue a Null value with a Space in Query

[crhodus]

In the database that I'm working with, there are a few records that have null values in the zip code. I've created an expression called CityStateZip that I'm using for an address line on a report.

SELECT FirstName+" "+ LastName AS JoinedName, Address, City]+", "+State+" "+Zip AS CityStZip, from MyTable.

Whenever I view the results of the query, the CityStZip is blank for certain records that have a null value in the zip code.

If the zip is null, I still want City, State to appear on the report.

How can I have my query to replace a null value with a space when this occurs?

Thanks,
crhodus

 

[Banana]

You don't have to replace null with a space. You only need a Nz function. search/read on Nz funciton.

 

[crhodus]

Thanks for the info. The Nz function is just what I needed.

SELECT FirstName+" "+ LastName AS JoinedName, Address, City]+", "+State+" "+ Nz(Zip,"") AS CityStZip, from MyTable.

Crhodus