How to convert zeros in a Query in Access 2003

[Alchemist61]

I am running a query based on two tables and have created the outer left join
relationship in the query which works beautiful.
when I run the Query, It returns Quantity Shipped this month and Quantity
shipped todate. If an item does not shipped in this month, it would return a
blank field in the Query. Can you help me to create an Expression in the
Query Criteria that would convert only the Blanks into Zeros? Please make it
simple for a beginner.
Thank You.

 

[Clif McIrvin]

I am running a query based on two tables and have created the outer
left join
relationship in the query which works beautiful.
when I run the Query, It returns Quantity Shipped this month and
Quantity
shipped todate. If an item does not shipped in this month, it would
return a
blank field in the Query. Can you help me to create an Expression in
the
Query Criteria that would convert only the Blanks into Zeros? Please
make it
simple for a beginner.
Thank You.

You will need to make certain that the names are correct; and keep the
square brackets [].

I don't know if this can be done without changing the name at the top of
the column in the Query results:

Try this in the Field grid of the query design view:
[new Quantity Shipped this month]: nz([Quantity Shipped this month],0)

and leave everything else the same as you have it.

nz(<<expr>>,0) replaces nulls with zeroes (but does not change the data
in the table.)

HTH

 

[KARL DEWEY]

Post the SQL of your query for a look-see.
Open the query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[Alchemist61]

Tanks Guys. Below is the SQL View.
SELECT smsYTDqry.Categories, smsYTDqry.CarClass, smsYTDqry.Location,
smsMTDqry.SumOfQuantity, smsYTDqry.SumOfQuantity, smsYTDqry.Comments
FROM smsYTDqry LEFT JOIN smsMTDqry ON (smsYTDqry.Location =
smsMTDqry.Location) AND (smsYTDqry.CarClass = smsMTDqry.CarClass) AND
(smsYTDqry.Categories = smsMTDqry.Categories);

 

[KARL DEWEY]

Try this --
SELECT smsYTDqry.Categories, smsYTDqry.CarClass, smsYTDqry.Location,
Nz([smsMTDqry].[SumOfQuantity],0) AS MTDSumOfQuantity,
Nz([smsYTDqry].[SumOfQuantity],0) AS YTDSumOfQuantity, smsYTDqry.Comments
FROM smsYTDqry LEFT JOIN smsMTDqry ON (smsYTDqry.Location =
smsMTDqry.Location) AND (smsYTDqry.CarClass = smsMTDqry.CarClass) AND
(smsYTDqry.Categories = smsMTDqry.Categories);

 

[Alchemist61]

Karl, I want to thank you very much. I was fighting with this for a long
time, now I have peace of mind. Works perfectly.
Thanks again.

Try this --
SELECT smsYTDqry.Categories, smsYTDqry.CarClass, smsYTDqry.Location,
Nz([smsMTDqry].[SumOfQuantity],0) AS MTDSumOfQuantity,
Nz([smsYTDqry].[SumOfQuantity],0) AS YTDSumOfQuantity, smsYTDqry.Comments
FROM smsYTDqry LEFT JOIN smsMTDqry ON (smsYTDqry.Location =
smsMTDqry.Location) AND (smsYTDqry.CarClass = smsMTDqry.CarClass) AND
(smsYTDqry.Categories = smsMTDqry.Categories);

--
KARL DEWEY
Build a little - Test a little

Tanks Guys. Below is the SQL View.
SELECT smsYTDqry.Categories, smsYTDqry.CarClass, smsYTDqry.Location,
smsMTDqry.SumOfQuantity, smsYTDqry.SumOfQuantity, smsYTDqry.Comments
FROM smsYTDqry LEFT JOIN smsMTDqry ON (smsYTDqry.Location =
smsMTDqry.Location) AND (smsYTDqry.CarClass = smsMTDqry.CarClass) AND
(smsYTDqry.Categories = smsMTDqry.Categories);