Here is the situation. I tried to create a query from a Excel table.

If I use following, I get result back without problem when the column type is character (alphabic):

SELECT format(nz(afile.[COLUMN1]," "), " ")
FROM afile;

However, if the column is not alphabic, but with numbers, such as 1234, 2345, even I set them as 'Text', there is nothing back.

If I change it to this way:

SELECT format(nz(afile.[COLUMN1],"0000"), "0000")
FROM afile;

I got the data back and when there is a null value, I got 0000. However, I don't want '0000', I want null.

So what should I do to query on the numbers and get null value when the value is null?

Thanks,
Ning