Full Version: DCount and Null
UtterAccess Forums > Microsoft® Access > Access Forms
wind54surfer
Hi,
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?
TIA,
Emilio
mishej
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:
CODE
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?
wind54surfer
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.
xteam
try using this expression for Checkbox ControlSource:
IIF(DCount("[Status]","]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") >0, False, True)
mishej
OK, I see. Yes, you are right - its backwards. Try this:
DCount("[Scheduled]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status <> 'Done'") = 0
wind54surfer
I tried and get:
"The expression you entered has a function containing the wrong number of arguments"
xteam
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.
wind54surfer
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.
xteam
try this...
DCount("[Status]","[JobSchedule]","[Scheduled] = """ & [JobCustomerName] & """ AND Status & "" <> 'Done'") >0
mishej
Think that it should be:
CODE
=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...
wind54surfer
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
Else
Me!btnPreSchedule.Visible = True

Oreally appreciate your help,
Emilio

Edited by: wind54surfer on Sun Nov 28 10:32:15 EST 2004.
Edited by: wind54surfer on Sun Nov 28 10:58:06 EST 2004.
wind54surfer
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")
xteam
Ty to get rid of nulls adding an empty string:
.. Status & '' <> ""Done""")
LittleViews
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.