DCount with a twist

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

Guest

I have to count the number of records in a particular field but some are
blank (meaning there was no response to that question).
I first have to limit the count to records with a particular office number,
then look in the field "Other" and count the number of responses [For that I
am not interested in a particular response, just want it to count any
response in that field].
Here's what I have for my control in the report:

=DCount("[Office]" , "2005 Files", "[Office]='100' AND [Other]='*' ")

Now, I would think that using the * would pick up any text entered in that
field. But the answer I get is zero when it should give me 2.
Does anyone have any suggestions that would help?
Thank you very much.
 
You must use "Like" to match with wildcards.
=DCount("[Office]" , "[2005 Files]", "[Office]='100' AND Not
IsNull([Other])")
This assumes the Office field is text (not numeric).
--
Duane Hookom
MS Access MVP


"limited computer knowledge"
 
Duane,
For my info - I really am curious -, why did you use
NOT IsNull([Other])
Vice
[Other] is Not Null

Is the first more reliable or faster or just an alternative way of doing
things?


Duane Hookom said:
You must use "Like" to match with wildcards.
=DCount("[Office]" , "[2005 Files]", "[Office]='100' AND Not
IsNull([Other])")
This assumes the Office field is text (not numeric).
--
Duane Hookom
MS Access MVP


"limited computer knowledge"
I have to count the number of records in a particular field but some are
blank (meaning there was no response to that question).
I first have to limit the count to records with a particular office
number,
then look in the field "Other" and count the number of responses [For
that I
am not interested in a particular response, just want it to count any
response in that field].
Here's what I have for my control in the report:

=DCount("[Office]" , "2005 Files", "[Office]='100' AND [Other]='*' ")

Now, I would think that using the * would pick up any text entered in
that
field. But the answer I get is zero when it should give me 2.
Does anyone have any suggestions that would help?
Thank you very much.
Click to expand...
Click to expand...
 
I'm just an IsNull() kinda guy. No special reason.

--
Duane Hookom
MS Access MVP


John Spencer said:
Duane,
For my info - I really am curious -, why did you use
NOT IsNull([Other])
Vice
[Other] is Not Null

Is the first more reliable or faster or just an alternative way of doing
things?


Duane Hookom said:
You must use "Like" to match with wildcards.
=DCount("[Office]" , "[2005 Files]", "[Office]='100' AND Not
IsNull([Other])")
This assumes the Office field is text (not numeric).
--
Duane Hookom
MS Access MVP


"limited computer knowledge"
I have to count the number of records in a particular field but some are
blank (meaning there was no response to that question).
I first have to limit the count to records with a particular office
number,
then look in the field "Other" and count the number of responses [For
that I
am not interested in a particular response, just want it to count any
response in that field].
Here's what I have for my control in the report:

=DCount("[Office]" , "2005 Files", "[Office]='100' AND [Other]='*' ")

Now, I would think that using the * would pick up any text entered in
that
field. But the answer I get is zero when it should give me 2.
Does anyone have any suggestions that would help?
Thank you very much.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top