Re: Default value return

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It’s a combination of fields. I have check boxes (all in one table) for
export, truck, and can. There is also one for rail but I don’t want any
records for rail included in these results.

I need the query to return a value of zero for time frames when there aren’t
any loads of a particular kind shipped (ex. Export trucks). This way I can
import the query results into a report and have it displaying in a uniform
order time after time even when there’s nothing shipped.

I hope this makes it all clearer.

Thanks,
Dragon

"John Spencer (MVP)" wrote:

Would you care to explain which field returns the values of
Non Export, Export Trucks and Export Cans? Or is that a combination of fields?

Do you have a separate table that has those values in it?

And NZ has to have a field/object to work on to return anything. If there is
no row, then NZ has no field to work with. So NZ is probably not the solution
for your problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dragon wrote:
When there are no records it simply doesn't display anything for that option.
I'm not familiar with the Nz() command recommended.
How would it fit into my query?

Thanks,
Dragon

"pietlinden@xxxxxxxxxxx" wrote:

On Jan 20, 4:09 pm, Dragon <Dra...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have a query that counts my trailers loaded in a given day for a given
warehouse. I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day. (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I need an
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
are you getting nulls? Use Nz() to convert the nulls to zeroes.


.

Relevant Pages

  • Re: Default value return
    ... Export Trucks and Export Cans? ... I need the query to return a default value of 0 for each ... Use Nzto convert the nulls to zeroes. ...
    (microsoft.public.access.queries)
  • Re: Linking data
    ... FROM (AllRecords LEFT JOIN Cars ... You were given instructions on how to build this query in the grid. ... One table has Trucks ...
    (microsoft.public.access.queries)
  • Default value return
    ... I have a query that counts my trailers loaded in a given day for a given ... (SQL below) ... Export Trucks: 0 ... Export Cans: 2 ...
    (microsoft.public.access.queries)
  • Help with counting total items using hierarchical data structure
    ... I'm having trouble coming up with a query to count the number of total ... seperate queries in loops and I know there must be an more efficient ... Automotive has 4 items within it (under Cars, Trucks & Vans) ...
    (comp.lang.php)
  • Re: Default value return
    ... I need the query to return a default value of 0 for each ... Export Trucks: 0 ... Export Cans: 2 ... Use Nzto convert the nulls to zeroes. ...
    (microsoft.public.access.queries)