Null value to Zero (1 Viewer)

[htadis]

Hi,

I have a query which i need to get Zero as a result if NULL value. How Can i do this ? Present SQL code is as follows;

SELECT tbl_Impts_main.Bkg_number, Count(tbl_Impts_main.Bkg_number) AS CountOfBkg_number
FROM tbl_Impts_main INNER JOIN tbl_booking ON (tbl_Impts_main.Voyage=tbl_booking.Voyage) AND (tbl_Impts_main.Vessel=tbl_booking.Vessel) AND (tbl_Impts_main.Bkg_number=tbl_booking.Bkg_number)
GROUP BY tbl_Impts_main.Bkg_number
HAVING (((tbl_Impts_main.Bkg_number)=[forms]![frm_Export_data_entry]![bkg_number]));

brgds

 

[plog]

Use the NZ function: http://www.techonthenet.com/access/functions/advanced/nz.php

 

[htadis]

thanks ! But it did not work. following error msg,

sytax error(Comma) in query expression

 

[plog]

Whats the expression you tried?

 

[htadis]

Expr1: Nz([bkg_number],0)

 

[Brianwarnock]

How can the bkg_number be Null or 0?

I'm suspicious that your problem is not that you have a Null value but that your query is returning nothing.

Try a Left join.

Brian

 

[htadis]

Brian,

yes, some instance my query is returning nothing. at that point i need to show '0' in the count column.

is that possible ?

brgds

 

[plog]

Do you have a datasource that contains all the results you want? You can't create values when there are none in the underlying data.

For example, if I had a sales table and wanted to produce a sales total by day for every day in the week and we were closed on Tuesday, I couldn't produce anything for Tuesday from that sales table. However, I could do it if I had another table that had Tuesday in it.

Do you have a datasource that contains all the values you want to report on?