Showing 0 if no results

[sroot]

I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?

Thanks!

 

[bob fitz]

I think you need to use NZ() function.
Perhaps something like:
Expr1:NZ([YourFieldName],0)

 

[plog]

You can't create records where none exist. You can however turn a field's value from null to 0 easily with the Nz function (http://www.techonthenet.com/access/functions/advanced/nz.php).

So, I'm unclear. Is the query returning no records? Or is it returning records but with null values that you want to be 0?

 

[sroot]

That was one of the first thing i tired and i still get a blank result

 

[bob fitz]

Can you respond to the point plog queried in post #3

So, I'm unclear. Is the query returning no records? Or is it returning records but with null values that you want to be 0?

 

[sroot]

Sorry missed that question. The query is returning no records. There are only records when the item is in the building. So if there is 10 locations of item 1234 it will sum the field "qty_on_hand" but if there are none in the building it doesn't show anything

 

[bob fitz]

Can you show us a screen shot of the query in design view

 

[sroot]

Its really not much. but i have a few other query that tie into this that i am going to have the same issue with.

 

[plog]

Do you have an item table? A source that list your unique items? If so, you should do this:

Bring that Item table into the query
Join that table to the table you are trying to get the sum from
Make that a LEFT JOIN from the Item table to the existing table
Pull the Item data from the Item table instead of the existing table.
Use NZ around the sum field you are trying to calculate.
Run your query and you will get your 0.

 

[sroot]

Plog that worked great. Thanks for that, i didn't even think about trying to do that!

 

[Pippa_McC]

Re: Showing 0 if no results (Solution)

Hi,

I know that this thread was from last year however, if anyone else is having the same issue, heres another fix:

Write your query as normal to count the number of occurences & save.

Open your form in design mode and click on your text box & open the properties window. Within Data > control source use:

=Nz(DLookUp("Count","yourQryName"),0)

"Count" is the name of the column. 0 is if the result is null..

Just for a bit of info, the double quotations force it into a string value, thus removes the #Name? error from the text box.

Hope this helps someone