Combine Iif & DCount?

[Kila]

I am trying to use the following statement to count records:

=DCount("[ID]","master","[facility] = [txtFacility]")

However, when my field [facility] is null, the count comes back as "0", even though there are many fields that have no facility. When this is the case, I want it to count the records for which the [facility] field is null. How can I do this? Can I combine Iif & DCount? Something like this....

=Iif (IsNull[facility], DCount("[ID]","master","IsNull[facility]"), DCount("[ID]","master","[facility] = [txtFacility]"))

However, when I do this, I get:

You may have entered an operand without an operator.

What is the correct way to handle this?

 

[yellow]

The reason you're getting that error is because you're missing () around the IsNull Statement. Change it to:

=IIf (IsNull([facility]), DCount("[ID]","master","IsNull[facility]"), DCount("[ID]","master","[facility] = [txtFacility]"))

 

[Kila]

Not quite...

Thank you...we are closer now, but it gives an #Error message for the Null fields, even though it WILL count the fields with data. How can I get around this? I read in the help files that DCount will not count Null fields without a "*", but I do not want to count ALL fields, only the empty ones.

I DID try

=IIf(IsNull([facility]),DCount("*","master")-DCount("[ID]","master"),DCount("[ID]","master","[facility] = [txtFacility]"))

...using the idea that we can subtract the # fields with data from the # of ALL fields, but this is returning a 0.

Any ideas?

Thanks for your help!

 

[Kila]

Got it!

For anyone who is interested, I finally got it to work like this:

=IIf(IsNull([facility]),DCount("[ID]","master","")-DCount("[ID]","master","[facility] like '*'"),DCount("[ID]","master","[facility] = [txtFacility]"))

Thanks for your help!

 

[Nirious]

Whats the difference between DCOUNT and COUNT? or DSUM and SUM etc.

So actually what's the diff between domain aggregate functions and 'normal' ones?

 

[WayneRyan]

Nirious,

DCount, DSum, DMax, DMin, etc. are Access's domain aggregate functions. They allow you to
traverse an Access table and collect information.

Count, Sum, Max, Min, etc. are SQL's aggregate functions. They allow for the collection
of data within queries.

Instr, Space, Len, etc. are "Normal" functions that perform some operation on a
single data element.

Wayne