Append query field value, put 0 instead of blank.

[MushroomKing]

Hey folks!

Im stuck with this query. I don't fully understand where im making the mistake here.

Some times, the query returns null (blank).
In that case i want the value to be 0.

The part i also dont understand:
It's append query. When i put the default value as 0 in the table it appends to, it doesn't work either.

How can i solve this query, and why does the default dont work in my table?

Thanks in advance!

 

[Ranman256]

use:
NZ([field],0)

 

[arnelgp]

yes, use Nz([quantity],0) instead of Nz(scanquantity)

 

[MushroomKing]

Thanks for reply Ranman!

Well, when i put NZ([scanquantity],0) it asks me for parameter when i run it.
When i put NZ([quantity],0) i get an error:


the specified field [quantity] could not refer to more than one table listed

what is happening?

 

[arnelgp]

then qualify with correct Table name:

NZ([TableNameHere].[quantity],0)

 

[MushroomKing]

Aaaah yes.

Indeed, i have 2 fields with quantity from different tables.
I thought access would figure this out because im telling it from which table.

But this is better

Thanks. Next time i will name my fields different in the different tables

Cheers!

 

[arnelgp]

cheers friend!

 

[MushroomKing]

Ai...not there yet.

Now it says:

Your query does not include the specified expression
Sum(Scanned.quantity)=Nz([Scanned].[quantity],0) as part of an
aggregate function

 

[arnelgp]

firstly, you cannot update an Aggregate query.
if you are not updating it, adding that Expression as Group (Group by Sum(Scanned.quantity)=Nz([Scanned].[quantity],0) may solve the problem.