How to setup default for number column in Access query?

[Ning]

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

[alansidman]

The NZ function allows Access to do calculations on Null fields in records by assigning some value (either numeric or text). If you do not want this to happen then don't use the NZ function.

[Ning]

Sorry. We have to use NZ function since many values in the table are null.

[alansidman]

I am not understanding. You are using NZ function because many of the field values are null but when you query the recordset you want the values to remain null. This make no sense to me. In Access the reason you would NZ is when you want null values to appear as something other than as Null values. I think you need to explain your situation with real life examples and perhaps even post a copy of your db.

[Ning]

In our case, we not only have to show the null values but also to show how many digits/characters in that field since everything is in a bulk file. That is the requirement. For instance, there are two groups that have interaction with outside sources (many of them). For past month though, one group has contacted with source '1234' and '5678' and the other none. The result for last month has to be: for group1, '1234', '5678' and for group2 ' ' (4 digits here). Or in the file it should look like this: xxxxxx1234yyyy, xxxxxx5678yyyy, xxxxxx yyyy.

Hopefully I answered your question.

Thanks,
Ning