Make fields that are blank display 0

[anthgav]

I have a query and one of the fields has blank values if there is nothing to add up, is there a way I can make the blank values display 0 instead? The field in question is "qryUsedProductSpacePerBay.used_space_mm"



Here is the SQL...

SELECT tblWarehouse.warehouse,
tblArea.area,
tblBay.bay,
tblBay.bay_width_mm,
tblBay.bay_depth_mm,
tblBay.bay_height_mm,
qryUsedProductSpacePerBay.used_space_mm,
[bay_width_mm]-[used_space_mm] AS free_space_mm,
tblBin.bin,
tblBin.bin_width_mm,
tblBinType.priority,
tblAllocatedBin.allocated_bin_id,
tblBinType.bin_type,
qryAllocatedProductAndBin.product,
tblProduct.width_mm,
tblProduct.depth_mm,
tblProduct.height_mm,
tblProduct.row, tblProduct.deep,
tblProduct.high, tblProduct.loose,
tblProduct.width_required,
tblProductData.long_description,
tblProduct.additional_info,
tblProductData.catalogue_number,
tblProductData.bin_number,
tblProductData.min_bin_qty,
tblProductData.max_bin_qty,
tblProductData.pallet_break,
tblProductData.physical_qty,
tblProductData.allocated_qty,
tblProductData.back_order_qty,
tblProductData.forward_order_qty,
tblProductData.on_order_qty,
tblProductData.quantity_in_bulk,
tblProductData.package,
tblProductData.demand_month_1, 
tblProductData.demand_month_2, 
tblProductData.demand_month_3, 
tblProductData.demand_month_4, 
tblProductData.demand_month_5, 
tblProductData.demand_month_6, 
tblProductData.demand_month_7, 
tblProductData.demand_month_8, 
tblProductData.demand_month_9, 
tblProductData.demand_month_10, 
tblProductData.demand_month_11, 
tblProductData.demand_month_12
FROM (qryUsedProductSpacePerBin RIGHT JOIN ((qrySumOfBinSize RIGHT JOIN (qryAllocatedProductAndBin RIGHT JOIN (((((tblWarehouse LEFT JOIN tblArea ON tblWarehouse.[warehouse_id] = tblArea.[warehouse]) LEFT JOIN tblBay ON tblArea.[area_id] = tblBay.[area]) LEFT JOIN tblBin ON tblBay.[bay_id] = tblBin.[bay]) LEFT JOIN (tblBinType RIGHT JOIN tblAllocatedBin ON tblBinType.[bin_type_id] = tblAllocatedBin.[allocated_bin_type])
ON tblBin.[bin_id] = tblAllocatedBin.[allocated_bin]) LEFT JOIN tblProduct ON tblAllocatedBin.allocated_bin_id = tblProduct.[allocated_bin_1]) ON qryAllocatedProductAndBin.allocated_product = tblAllocatedBin.allocated_bin_id) ON qrySumOfBinSize.bay = tblBay.bay) LEFT JOIN tblProductData ON tblProduct.product = tblProductData.product) ON qryUsedProductSpacePerBin.bay = tblBay.bay) LEFT JOIN qryUsedProductSpacePerBay ON tblBay.bay = qryUsedProductSpacePerBay.bay
WHERE (((tblBinType.bin_type) Is Not Null))
ORDER BY tblWarehouse.warehouse, tblArea.area, tblBay.bay, tblBin.bin, tblBinType.priority;

[ranman256]

convert nulls to zero: NZ([field],0)

[anthgav]

Thanks that works in Access but I use Excel to draw the data into a pivot table and it still shows as blank in the pivot table, any ideas why it doesn't translate to Excel via the query?

[anthgav]

I actually had a bit of a blonde moment and realised my pivot table was using a different front end file to the one I edited the query in which is why it was still showing blank.
I have a new problem, Excel doesn't like NZ([field],0) being in the query and fails to refresh when it's included, is there an alternative way of achieving making blanks show as 0 that might work when being pulled from Excel?

[anthgav]

This seems to be compatible with Excel "IIF(ISNULL(qryUsedProductSpacePerBay.used_space_m m), 0, qryUsedProductSpacePerBay.used_space_mm)"

[ssanfu]

You didn't specify the name of the query you posted in Post#1, so lets call it "Query1". (imaginative, No?)
"Query1" has the "NZ([field],0)" in it.

Create another query with "Query1" as the source. Add all of the fields to the grid. Name this query "qryExportForExcel".
Open query "qryExportForExcel". Are the zeros there now?

[anthgav]

You didn't specify the name of the query you posted in Post#1, so lets call it "Query1". (imaginative, No?)
"Query1" has the "NZ([field],0)" in it.

Create another query with "Query1" as the source. Add all of the fields to the grid. Name this query "qryExportForExcel".
Open query "qryExportForExcel". Are the zeros there now?

When "used_space_mm: Nz([qryUsedProductSpacePerBay.used_space_mm],0) AS used_space_mm" is in the query Excel doesn't even detect the query so i can't select it, even after doing what you suggested by creating "qryExportForExcel".
If I use "IIF(ISNULL(qryUsedProductSpacePerBay.used_spa ce_m m), 0, qryUsedProductSpacePerBay.used_space_mm) AS used_space_mm" in the query then Excel detects it and pulls through ok.
This field "[bay_width_mm]-[used_space_mm] AS free_space_mm" uses the other field for a calculation but if "used_space_mm" is 0 then "free_space_mm" does not pull through to Excel in the report even though the figure shows up in Access.

Not sure what I can do to make it pull through to Excel. Are there any other ways of achieving this?

[anthgav]

I managed to figure out a solution to make it compatible with the Excel report. The calculation in the "free_space_mm" field also needs to convert Null values to zeros despite the fact the lookup field already does it before hand.
So i did "[bay_width_mm]-IIf(IsNull([used_space_mm]),0,[used_space_mm]) AS free_space_mm" Now this field displays correctly in Excel.

Thanks guys you pointed me in the correct direction.