Solved Convert Null to zeroo (1 Viewer)

[nirmal]

how to insert a default value of zero in a access query if the calculated result is null

So I converted it to Nz statement, still no results i.e. Null not converted to Zero
Here is my sql statement

SELECT
AgeAll_qry.Month,
Nz(AgeAll_qry.CountOfAge,0) AS Expr1,
Nz([Age<18_qry].CountOfAge,0) AS Expr2,
Nz([Age18-30_qry].CountOfAge,0) AS Expr3,
Nz([Age31-35_qry].CountOfAge,0) AS Expr4,
Nz([Age>35_qry].CountOfAge,0) AS Expr5
FROM AgeAll_qry, [Age<18_qry], [Age18-30_qry], [Age31-35_qry], [Age>35_qry];

 

[theDBguy]

If Nz() doesn't work, then maybe it's not Null. Check for "" instead. For example:

IIf(CountofAge="",0,CountOfAge)

Hope that helps...

 

[The_Doc_Man]

You are using the correct formula - NZ( variable-or-field, value-if-null )

Are you having other issues? Is something not working that would lead to this question?

 

[June7]

Why no JOIN clauses? Without any output is a Cartesian association of records. Each record of every table will associate with each record of other tables. With 5 tables, this could result in a massive dataset that performs very slowly. Why are these counts not all in one query? Provide sample raw data. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[plog]

I liked her post but need to add more: While the others are giving you good advice about Counting and NZ, June's advice is looking at the big picture and should be the one you follow.

 

[The_Doc_Man]

Good catch, June7.

 

[arnelgp]

If Nz() doesn't work, then maybe it's not Null. Check for ""

is it possible?
Using Count will always return number (either 0 or a value).


SELECT TOP 1
DLookup("CountOfAge","AgeAll_qry") As Expr1
Dlookup("CountOfAge","[Age<18_qry]") AS Expr2,
Dlookup("CountOfAge","[Age18-30_qry]") AS Expr3,
Dlookup("CountOfAge","[Age31-35_qry]") AS Expr4,
Dlookup("CountOfAge","[Age>35_qry]") AS Expr5
FROM AgeAll_qry;

 

[nirmal]

is it possible?
Using Count will always return number (either 0 or a value).


SELECT TOP 1
DLookup("CountOfAge","AgeAll_qry") As Expr1
Dlookup("CountOfAge","[Age<18_qry]") AS Expr2,
Dlookup("CountOfAge","[Age18-30_qry]") AS Expr3,
Dlookup("CountOfAge","[Age31-35_qry]") AS Expr4,
Dlookup("CountOfAge","[Age>35_qry]") AS Expr5
FROM AgeAll_qry;

No Sir, not getting the expected count of zero, if there is no person within that age range. All results go blank, even if a single CountOfAge is blank

 

[nirmal]

If Nz() doesn't work, then maybe it's not Null. Check for "" instead. For example:

IIf(CountofAge="",0,CountOfAge)

Hope that helps...

Tried this

SELECT
IIf(AgeAll_qry.CountOfAge=" ", 0, AgeAll_qry.CountOfAge) AS Expr1,
IIf([Age<18_qry].CountOfAge=" ", 0, [Age<18_qry].CountOfAge) AS Expr2,
IIf([Age18-30_qry].CountOfAge=" ", 0, [Age18-30_qry].CountOfAge) AS Expr3,
IIf([Age31-35_qry].CountOfAge=" ", 0 ,[Age31-35_qry].CountOfAge) AS Expr4,
IIf([Age>35_qry].CountOfAge=" ", 0, [Age>35_qry].CountOfAge) AS Expr5
FROM AgeAll_qry, [Age<18_qry], [Age18-30_qry], [Age31-35_qry], [Age>35_qry];

But now all fields showing #Error

 

[nirmal]

is it possible?
Using Count will always return number (either 0 or a value).


SELECT TOP 1
DLookup("CountOfAge","AgeAll_qry") As Expr1
Dlookup("CountOfAge","[Age<18_qry]") AS Expr2,
Dlookup("CountOfAge","[Age18-30_qry]") AS Expr3,
Dlookup("CountOfAge","[Age31-35_qry]") AS Expr4,
Dlookup("CountOfAge","[Age>35_qry]") AS Expr5
FROM AgeAll_qry;

Tried Sir your code, still same problem persists. When the count if null in any column all fields go blank

 

[arnelgp]

go a little further:

SELECT TOP 1
Nz(DLookup("CountOfAge","AgeAll_qry"), 0) As Expr1
Nz(Dlookup("CountOfAge","[Age<18_qry]"), 0) AS Expr2,
Nz(Dlookup("CountOfAge","[Age18-30_qry]"), 0) AS Expr3,
Nz(Dlookup("CountOfAge","[Age31-35_qry]"), 0) AS Expr4,
Nz(Dlookup("CountOfAge","[Age>35_qry]"), 0) AS Expr5
FROM AgeAll_qry;

 

[nirmal]

Why no JOIN clauses? Without any output is a Cartesian association of records. Each record of every table will associate with each record of other tables. With 5 tables, this could result in a massive dataset that performs very slowly. Why are these counts not all in one query? Provide sample raw data. If you want to provide db for analysis, follow instructions at bottom of my post.

Sir Attaching zipped file for analysis. Actually other than single age count query, can we have a single issues count query. HELP

 

[nirmal]

go a little further:

SELECT TOP 1
Nz(DLookup("CountOfAge","AgeAll_qry"), 0) As Expr1
Nz(Dlookup("CountOfAge","[Age<18_qry]"), 0) AS Expr2,
Nz(Dlookup("CountOfAge","[Age18-30_qry]"), 0) AS Expr3,
Nz(Dlookup("CountOfAge","[Age31-35_qry]"), 0) AS Expr4,
Nz(Dlookup("CountOfAge","[Age>35_qry]"), 0) AS Expr5
FROM AgeAll_qry;

No Sir, this also not working and not fetching desired zero value count

 

[The_Doc_Man]

When I open your queries in design view, I see something that absolutely CANNOT work. Your "age" queries perform a GROUP BY based on the values in your unbound combo boxes on your form. A WHERE clause might work based on a form reference but a GROUP BY will not.

A SELECT query points to a record source. The FROM names the source, the field list of the SELECT names data fields, but if you have ORDER BY or GROUP BY clauses, they MUST refer to fields within your data source (table or query). In your age queries, they do not. They reference something that is totally outside the data source. Only a WHERE clause can possibly refer to non-table elements.

My suggestion is that you scrap whatever queries you did here and tell us in conversational language what it is that you wanted to do. Because those queries can NEVER work and are confused enough that I cannot fix them without knowledge of your intent.

 

[arnelgp]

see Query1(age) and Query1(issues).

 

[nirmal]

When I open your queries in design view, I see something that absolutely CANNOT work. Your "age" queries perform a GROUP BY based on the values in your unbound combo boxes on your form. A WHERE clause might work based on a form reference but a GROUP BY will not.

A SELECT query points to a record source. The FROM names the source, the field list of the SELECT names data fields, but if you have ORDER BY or GROUP BY clauses, they MUST refer to fields within your data source (table or query). In your age queries, they do not. They reference something that is totally outside the data source. Only a WHERE clause can possibly refer to non-table elements.

My suggestion is that you scrap whatever queries you did here and tell us in conversational language what it is that you wanted to do. Because those queries can NEVER work and are confused enough that I cannot fix them without knowledge of your intent.

Sir I want to get the total number of customers in certain month of certain age groups like < 18 year, 18-30 years, 31-35 years and > 35 years
Similarly I want the know the total number of customers in certain month who have no living issues, 1 living Male only, 1 living Female only, 2 or > Living Males only, 2 or > Living Females only and Rest other combination of Living Males And Females

 

[Pat Hartman]

Using Count will always return number (either 0 or a value).

Count() counts the rows in the domain. It doesn't count individual values. SQL Server has a predicate that does that but not Access.

 

[June7]

Saving Age as a static value causes issues. People age. Next year all these values will be wrong. No customers over 35?

Calculating age groups is simple:

SELECT Month([DateCreated]) AS Mo, Year([DateCreated]) AS Yr, IIf([Age]<18,[Age],Null) AS [Age<18], IIf([Age]>=18 And [Age]<=30,[Age],Null) AS Age18_30, IIf([Age]>30 And [Age]<=35,[Age],Null) AS Age31_35, Customer_tbl.SerialNumber AS [All]
FROM Customer_tbl;

Can add grouping and filter criteria to that SQL. Could also use it as source for a report and use report Sorting & Grouping with aggregate calcs in textbox. Apply filter criteria to report, not query. This allows display of detail data as well as summary calcs.

 

[arnelgp]

see your form.

 

[nirmal]

Saving Age as a static value causes issues. People age. Next year all these values will be wrong. No customers over 35?

Calculating age groups is simple:

SELECT Month([DateCreated]) AS Mo, Year([DateCreated]) AS Yr, IIf([Age]<18,[Age],Null) AS [Age<18], IIf([Age]>=18 And [Age]<=30,[Age],Null) AS Age18_30, IIf([Age]>30 And [Age]<=35,[Age],Null) AS Age31_35, Customer_tbl.SerialNumber AS [All]
FROM Customer_tbl;

Can add grouping and filter criteria to that SQL. Could also use it as source for a report and use report Sorting & Grouping with aggregate calcs in textbox. Apply filter criteria to report, not query. This allows display of detail data as well as summary calcs.

Thank You Sir

SELECT

Month([DateCreated]) AS [Month],
Year([DateCreated]) AS [Year],
Count(IIf([Age]<18,[Age],Null)) AS [Age<18],
Count(IIf([Age]>=18 And [Age]<=30,[Age],Null)) AS Age18_30,
Count(IIf([Age]>30 And [Age]<=35,[Age],Null)) AS Age31_35,
Count(IIf([Age]>35,[Age],Null)) AS [Age>35],
Count(Customer_tbl.SerialNumber) AS [All]

FROM Customer_tbl

GROUP BY Month([DateCreated]), Year([DateCreated])

HAVING (((Month([DateCreated]))=[Forms]![MonthlyReportSearch_frm]![cboMonth]) AND ((Year([DateCreated]))=[Forms]![MonthlyReportSearch_frm]![cboYear]));


This SQL statement is giving me the expected results

Thank You once again