Query return value is Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Query:

Age Gender Age
Table Table Table
Count Group By Where
0 and < 14
Click to expand...

As long as there is a value that falls within the search criteria values are
set. The problem I have is when there is no age within the paramters (this is
valid). The query is empty.

Age Gender (Only two values that are passed on)

What I need is:
Age Gender
0

I need a zero in the Age count. I have tried several methods to try an get
a zero in this item if there are no entries but nothing I try works.
What am I missing?
Thanks for help.
 
Add this to your criteria statement --
Or Is Null

If this does not work for you then post your query SQL statement so it can
be checked.
 
Larry,

Two comments. In the query

Age Gender Age
Table Table Table
Count Group By Where
0 and < 14
Click to expand...

you should do the following. 1. Rename your table. Don't use the name "Table",
as "Table" is a reserved word in Access. Using a reserved word as the name of
an object, variable, function, etc. will produce "unpredictable results" (i.e.
possible garbage). 2. Regarding the criteria ">0 and < 14". Did you ever meet
anyone whose age was <= 0? No? I didn't either. Saying "<14" will get the
point across to Access/SQL; you can safely lose the beginning of the clause.

Regarding your original question. If I understand you correctly, you want a
"0" to show up in the CountOfAge field if there are no records that meet the
criteria. Unfortunately, Since you're using a Totals query (a query that uses
aggregate functions to consolidate data) it can't happen. There simply won't
be any display of data at all.

What you can do is, instead of displaying your data as a datasheet, simply
use the MsgBox statement to display the data, based on the information
available to it. Something like this. I'm going to call your query
qryCountOfAge for this purpose.

In VBA, you might have a routine like this:

Private Sub DispInfo

Dim Rst As DAO.Recordset, strMsg As String

Set Rst = CurrentDb.OpenRecordset("qryCountOfAge",dbOpenDynaset)
If Rst.RecordCount = 0 Then
MsgBox "There Are No Records in This Age Group"
Else
strMsg = ""
With Rst
.MoveFirst
Do While Not .EOF
strMsg = strMsg & "There Are " & CountOfAge & IIf(Gender =
"M", " Boys", " Girls ") & " In This Group" & Chr(10) & Chr(13)
.MoveNext
Loop
.Close
End With
MsgBox strMsg
End If
End Sub

This might produce either of the following messages:

"There Are No Records in This Age Group"

Or

"There Are 5 Boys In This Group"
"There Are 8 Girls In This Group"

Of course my numbers are pure fiction. I just wanted to show how you can
display data with finesse without using an actual datasheet or report.

Hope this helps,

Sam

KARL said:
Add this to your criteria statement --
Or Is Null

If this does not work for you then post your query SQL statement so it can
be checked.
Query:
Click to expand...
[quoted text clipped - 17 lines]
What am I missing?
Thanks for help.
Click to expand...
Click to expand...
 
To all:
Thanks for help, I will add this information to my library.

OfficeDev18 via AccessMonster.com said:
Larry,

Two comments. In the query

Age Gender Age
Table Table Table
Count Group By Where
0 and < 14
Click to expand...

you should do the following. 1. Rename your table. Don't use the name "Table",
as "Table" is a reserved word in Access. Using a reserved word as the name of
an object, variable, function, etc. will produce "unpredictable results" (i.e.
possible garbage). 2. Regarding the criteria ">0 and < 14". Did you ever meet
anyone whose age was <= 0? No? I didn't either. Saying "<14" will get the
point across to Access/SQL; you can safely lose the beginning of the clause.

Regarding your original question. If I understand you correctly, you want a
"0" to show up in the CountOfAge field if there are no records that meet the
criteria. Unfortunately, Since you're using a Totals query (a query that uses
aggregate functions to consolidate data) it can't happen. There simply won't
be any display of data at all.

What you can do is, instead of displaying your data as a datasheet, simply
use the MsgBox statement to display the data, based on the information
available to it. Something like this. I'm going to call your query
qryCountOfAge for this purpose.

In VBA, you might have a routine like this:

Private Sub DispInfo

Dim Rst As DAO.Recordset, strMsg As String

Set Rst = CurrentDb.OpenRecordset("qryCountOfAge",dbOpenDynaset)
If Rst.RecordCount = 0 Then
MsgBox "There Are No Records in This Age Group"
Else
strMsg = ""
With Rst
.MoveFirst
Do While Not .EOF
strMsg = strMsg & "There Are " & CountOfAge & IIf(Gender =
"M", " Boys", " Girls ") & " In This Group" & Chr(10) & Chr(13)
.MoveNext
Loop
.Close
End With
MsgBox strMsg
End If
End Sub

This might produce either of the following messages:

"There Are No Records in This Age Group"

Or

"There Are 5 Boys In This Group"
"There Are 8 Girls In This Group"

Of course my numbers are pure fiction. I just wanted to show how you can
display data with finesse without using an actual datasheet or report.

Hope this helps,

Sam

KARL said:
Add this to your criteria statement --
Or Is Null

If this does not work for you then post your query SQL statement so it can
be checked.
Query:
Click to expand...
[quoted text clipped - 17 lines]
What am I missing?
Thanks for help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top