Default value return

[Dragon]

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;

 

[pietlinden]

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 needan
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.

 

[Dragon]

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

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.

 

[John Spencer (MVP)]

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

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

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.

 

[Dragon]

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

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

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

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.