Help with count

  • Thread starter Thread starter Steve G
  • Start date Start date
S

Steve G

Hi All:

Back again :(

Access 2002

I am trying to determine the record acount being returned in a query. So
long as there are records meeting the criteria of the query, using the
aggregate function of count, it works well. However, if there are no records
returned, I need to display a count of 0 instead of just a blank recordset.

I've tried creating a new query, using the original query as the record set
and the following iif statement:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

However, when I run this query, I either get a blank record set, or if I use
this with criteria that I know will return a count, I get a value of 0
instead of the actual count.

Can anyone help me out with this?

TIA

Steve G
 
First of all remove the NOT,

As you have it:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

says: Take CountOfPatientStatus and IF it IS NOT NULL (Has a value in it)
display 0. So it gives you a value of zero when you know that there is a
value because that is what you told it to do.

Sean
 
I think I may not have explained the issue correctly above.
In a report, I need to show the number of records that meet certain criteria
such as discharged, pending, approved, etc.

I am using a series of underlying queries that will open a recordset based
on criteria, and using the aggregate function, will give me a count. Again,
this works well if there is a record that meets the criteria. If not, when I
run the query, I get the headers in the resultant view, but no fields for
information below it. I suspect this is why using the IIF statement in my
previous post to create a value of 0 is not working, since there are no
fields being displayed with any kind of value, null, blank, numerical or
anything else.

Does this make any sense?

Steve G
 
The function is: IIf(expr, truepart, falsepart)

You left out the false part. Try this:
(watch for line wrap)

IIf(IsNull([CountOfPatientStatus]), 0,
[CountOfPatientStatus])

If you want the zero to be text, put the quotes back ("0").

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
Thanks Sean:

I have tried that, however it just displays a blank recordset, instead of
the value of 0.

Steve G

Sean said:
First of all remove the NOT,

As you have it:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

says: Take CountOfPatientStatus and IF it IS NOT NULL (Has a value in it)
display 0. So it gives you a value of zero when you know that there is a
value because that is what you told it to do.

Sean

Hi All:

Back again :(

Access 2002

I am trying to determine the record acount being returned in a query. So
long as there are records meeting the criteria of the query, using the
aggregate function of count, it works well. However,
Click to expand...
if there are no
records
returned, I need to display a count of 0 instead of
Click to expand...
just a blank
recordset.
I've tried creating a new query, using the original
Click to expand...
query as the record
set
and the following iif statement:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

However, when I run this query, I either get a blank
Click to expand...
record set, or if I
use
this with criteria that I know will return a count, I get a value of 0
instead of the actual count.

Can anyone help me out with this?

TIA

Steve G
Click to expand...
Click to expand...


.
Click to expand...
 
I am pretty sure we understand what it is you are asking. SteveS's solution
would work if [CountOfPatientStatus] actually is NULL and not ZERO. It
depends on how you are calculating [CountOfPatientStatus]. Try his solution
and post back with more detail on how you are calculating
COUNTOfPatienStatus if it doesn't work.

Sean


Steve G said:
I think I may not have explained the issue correctly above.
In a report, I need to show the number of records that meet certain criteria
such as discharged, pending, approved, etc.

I am using a series of underlying queries that will open a recordset based
on criteria, and using the aggregate function, will give me a count. Again,
this works well if there is a record that meets the criteria. If not, when I
run the query, I get the headers in the resultant view, but no fields for
information below it. I suspect this is why using the IIF statement in my
previous post to create a value of 0 is not working, since there are no
fields being displayed with any kind of value, null, blank, numerical or
anything else.

Does this make any sense?

Steve G


Steve G said:
Hi All:

Back again :(

Access 2002

I am trying to determine the record acount being returned in a query. So
long as there are records meeting the criteria of the query, using the
aggregate function of count, it works well. However, if there are no records
returned, I need to display a count of 0 instead of just a blank recordset.

I've tried creating a new query, using the original query as the record set
and the following iif statement:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

However, when I run this query, I either get a blank record set, or if I use
this with criteria that I know will return a count, I get a value of 0
instead of the actual count.

Can anyone help me out with this?

TIA

Steve G
Click to expand...
Click to expand...
 
Thanks Steve:
I really appreciate your working with me on this.

Using this:
Count: IIf(IsNull([CountOfPatientStatus]), 0, [CountOfPatientStatus]) // I
added the header Count

When I run the query, I get a view that shows the header Count, but nothing
else...no fields below the header, so obviously no data.

Steve G

SteveS said:
The function is: IIf(expr, truepart, falsepart)

You left out the false part. Try this:
(watch for line wrap)

IIf(IsNull([CountOfPatientStatus]), 0,
[CountOfPatientStatus])

If you want the zero to be text, put the quotes back ("0").

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
Thanks Sean:

I have tried that, however it just displays a blank recordset, instead of
the value of 0.

Steve G

Sean said:
First of all remove the NOT,

As you have it:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

says: Take CountOfPatientStatus and IF it IS NOT NULL (Has a value in it)
display 0. So it gives you a value of zero when you know that there is a
value because that is what you told it to do.

Sean

Hi All:

Back again :(

Access 2002

I am trying to determine the record acount being returned in a query. So
long as there are records meeting the criteria of the query, using the
aggregate function of count, it works well. However, if there are no
records
returned, I need to display a count of 0 instead of just a blank
recordset.

I've tried creating a new query, using the original query as the record
set
and the following iif statement:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

However, when I run this query, I either get a blank record set, or if I
use
this with criteria that I know will return a count, I get a value of 0
instead of the actual count.

Can anyone help me out with this?

TIA

Steve G
Click to expand...


.
Click to expand...
Click to expand...
 
Steve G,

Make a query with the only item being [CountOfPatientStatus]. What is the
value that it gives you? I believe you are not getting the results you want
because Access is either defining or evaluating something differently than
you think it is or should.

Do I have this right?:
1) [CountOfPatientStatus] is a number (an integer is my guess).

2) By the name, I am assuming that this is counting the number of patients
with a certain status assigned to them (e.g. Released, Triage, etc. -- I
don't know medical terminology for this stuff)

3) It is possible for there to be NO patients with a particular status --
for which the count should be ZERO -- and this is where you have run into
problems.

This is no big problem to deal with especially if you just want to do it
using queries. So, let us know.

Sean

Steve G said:
Thanks Steve:
I really appreciate your working with me on this.

Using this:
Count: IIf(IsNull([CountOfPatientStatus]), 0, [CountOfPatientStatus]) // I
added the header Count

When I run the query, I get a view that shows the header Count, but nothing
else...no fields below the header, so obviously no data.

Steve G

SteveS said:
The function is: IIf(expr, truepart, falsepart)

You left out the false part. Try this:
(watch for line wrap)

IIf(IsNull([CountOfPatientStatus]), 0,
[CountOfPatientStatus])

If you want the zero to be text, put the quotes back ("0").

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
Thanks Sean:

I have tried that, however it just displays a blank recordset, instead of
the value of 0.

Steve G

First of all remove the NOT,

As you have it:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

says: Take CountOfPatientStatus and IF it IS NOT NULL (Has a value in it)
display 0. So it gives you a value of zero when you know that there is a
value because that is what you told it to do.

Sean

Hi All:

Back again :(

Access 2002

I am trying to determine the record acount being returned in a query. So
long as there are records meeting the criteria of the query, using the
aggregate function of count, it works well. However, if there are no
records
returned, I need to display a count of 0 instead of just a blank
recordset.

I've tried creating a new query, using the original query as the record
set
and the following iif statement:
Count: IIf(Not IsNull([CountOfPatientStatus]),"0")

However, when I run this query, I either get a blank record set, or if I
use
this with criteria that I know will return a count, I get a value of 0
instead of the actual count.

Can anyone help me out with this?

TIA

Steve G






.
Click to expand...
Click to expand...
Click to expand...
 
Okay,

First, email me a copy of your database. I think I know what is going on
but just in case it's not I'd like to see what you have in the tables that
might be causing a problem. There are a couple of ways to do this. I think
a part of the problem may be that you are trying to have one query do too
many steps. It's much less of a headache to break this into a couple of
queries. The big question at this step is your "criteria" aka
PatientStatus. What you should do first is select the records with the
criteria you want with one query and THEN count them.

This is the general procedure I would use with queries:

First, create a query that selects the records based on the criteria you
want, e.g.:

qryPatientStatus
-----------------
Field:PatientStatus
Criteria:[Enter Patient Status]

Second, create a query based on qryPatientStatus to do the counting, e.g.:

qryCountOfPatientStatus
--------------------------
Field:CountOfPatientStatus:Count([PatientStatus])

Now, when you run qryCountOfPatientStatus it will prompt you to enter the
status you want to count. Then it will count the number of matches it
finds. If there are no patients with that status it will return the value
zero (also if the status entered does not exist).

If you don't want to ask the user for the PatientStatus (which could lead to
errors) what you would want to do is set up this two-query count for each
possible criterion and then give the user the choices on a form. So you
could have something like this:

qryPatientStatusCriteria1 qryCountof PatientStatusCriteria1
------------------------ ----------------------------------
field:Whatever field necessary field:Countof...:Count([field from
qry])
Criteria:Criteria you want

qryPatientStatusCriteria2 qryCOPSC2
-------------------------- ---------------------------
field:whatever field necessary field:COPSC2:Count([fieldfromqry])
Criteria:Criteria2

ETC.

Sean
(e-mail address removed)
 
Back
Top