Full Version: DCount and Null
UtterAccess Forums > Microsoft® Access > Access Forms
got help tonight on the following, in a check box works great excepts when Null (no value), gives me "True" instead of "False".
=DCount("*","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status = 'Done'")=DCount("*","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """")
Any ideas why?
I'm assuming you had a cut and paste malfunction with your formula. Not sure how this relates to a checkbox. Are you trying to see if there are any unfinished jobs for a customer? You could do that with

=DCount("*","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") > 0

Don't use "*" - this returns a count that includes Null values. From the on-line help:
The DCount function doesn't count records that contain Null values in the field referenced by expr, unless
expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the
total number of records, including those that contain Null fields.

Instead pick a field that you want to count.

=DCount("[Scheduled]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") > 0

I did this help? Can you clarify further?
Thanks for your help.
What I need is to turn the Check box "True" when all the entries are "Done"
Otried what you sugested and works the other way around.
try using this expression for Checkbox ControlSource:
IIF(DCount("[Status]","]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") >0, False, True)
OK, I see. Yes, you are right - its backwards. Try this:
DCount("[Scheduled]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") = 0
I tried and get:
"The expression you entered has a function containing the wrong number of arguments"
had a mistake, corrected:
=IIF(DCount("[Status]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") >0, False, True)
But John 's expression is more elegant, you may want to use that instead.
Tried and still get True in Null

Also noted that if when I open the form is in a record that has a value other than Done I get:

runtime error "2427"
"You entered an expression that has no value"
Edited by: wind54surfer on Sun Nov 28 1:37:54 EST 2004.
try this...
DCount("[Status]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status & "" <> 'Done'") >0
Think that it should be:
=DCount("[Status]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status  <> ""Done""") = 0

The quotes are easy to get wrong. I haven't tested this so it is possible I'm wrong as well...
I have been playing a long time with different versions and making all kind of changes and this is what works the best:

=DCount("[Status]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> ""Done""") = 0

except when Null (I get True)

And the worst problem is when I open the form in any record different than ALL "Done" I get:

Run-time error '2427'
You entered an expression that has no value.

and Debug highlights this : If Me!Done = True Then (on Form On Current)

Me!btnPreSchedule.Visible = False
Me!btnPreSchedule.Visible = True

Oreally appreciate your help,

Edited by: wind54surfer on Sun Nov 28 10:32:15 EST 2004.
Edited by: wind54surfer on Sun Nov 28 10:58:06 EST 2004.
I don't know what I did but got rid of the problem:
un-time error '2427"
You entered an expression that has no value.
My only problem remains: Null (still showing as "True")
Ty to get rid of nulls adding an empty string:
.. Status & '' <> ""Done""")
If you have problems related to status either equal or not equal to "done" you might want to check your status data. You might have a single space in front of the words you are using for status.
You might also want to try . . . . and status like '*Done*' . . . . or status not like '*Done*'
Also, I assume that you have a field called "done" as in me.Done. Are you sure that that field exists on your form?
Also, I'vd found that if I have bad SQL someplace, Access highlights the complete wrong thing because it doesn't troubleshoot SQL, but gets picky on the results. You should try commenting out whatever SQL line posts to me.Done, then manually give that field a phony value, then test to see if things work.
You might also try If not isEmpty(me.done) or not isNull(me.done)
Last, check your syntax, because your quotes look a little strange. Instead of escaping them, try using single quotes as they are easier to read. Your line below:
""" AND Status <> ""Done""") = 0
Becomes " AND Status <> 'Done' " )
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.