Null value as equaling 1

[Haji]

Hi,

I have a table of transactional data from a point of sale
system. When someone fills out an invoice in this POS
system, they often forget to put a 1 in the quantity
field. Therefore, in my data, the quantity is sometimes
missing. I run reports that then add up sales by item by
summing the quantity field. The problem is that I am
undercounting the sum because of the nulls. I can't
change my table or I might alter an import process that
has been set up. Is there a way in a query that assign a
null value in the quantity field to equal 1? I can
always create a make take and schedule it as well.

Thanks,

Haji

 

[Guest]

You can use an IIf() statment in a query to replace the null values. For
example:

AltField1:IIf(Isnull([Field1]), 1, [Field1])

If you put this into a query (replacing Field1 with the field you require),
it will replace a null value with the one, while still keeping the data
intact.

 

[Guest]

You can replace the null vaules in a query using the IIf() statement For
example:

IIf(isnull([Field1]), 1, [Field1])

This should replace only the null values to 1 while leaving the non-null
values as they are.

 

[John Vinson]

Is there a way in a query that assign a
null value in the quantity field to equal 1?

Yes; multiply by

Nz([quantity], 1)

The NZ function will return the value in its first argument unless
that value is NULL, otherwise the second argument will be returned.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[PC Datasheet]

How about setting the default value of the quantity field to 1?