Concatenation of numbers and text but only to display certain values?

Hi,

I have tried several ways of trying to get rid get of blanks from a new field called the UCN created by combing two tables in a query. I found that adding "Is Not Null" to the Criteria panel on the query will hide the blank cells in that field.

The next step I am doing is creating another query based on the previous query. In this query I concatenating different fields. The formula I am using:

APN: IIf([ISRAILROAD]="Y",9 & [UCN] & [TAX_RATE_AREA],9 & [UCN] & "00001")

Some fields are numbers and some are text but in total they have to be 10 characters long for an APN code.

When I do this query I then get a problem on some of my records since I am concatenating blank cells from the UCN field which ruins my final result table.

I tried using different ways to incorporate the Criteria but nothing seems to work.