Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1490
  • Last Modified:

Dcount function and null values

I am trying to check for fields that have missing foreign keys in my ExpensItems table.  I can get an accurate count when I run a query using Is Null in the criteria for the CostAllocationCd field.  Each of the attempts below returns #Error in the textbox control on my form.

Attempt #1
=DCount("[CostAllocationCd]","ExpensesItems","IsNull([CostAllocationCd])")

Attempt #2
=DCount("[CostAllocationCd]","ExpensesItems","Nz([CostAllocationCd],'')=''")

Any suggestions would be appreciated.  D. Carter
0
Carter-D
Asked:
Carter-D
  • 3
  • 2
  • 2
3 Solutions
 
jjafferrCommented:
Hi Carter-D,

=DCount("*","ExpensesItems","IsNull([CostAllocationCd])")

I hope this helps,

jaffer
0
 
ki_kiCommented:
Try 'not is null'. if that gives you all the non null records, you can count all the records using another lookup and substarct the non null...........substarction= null!
0
 
ki_kiCommented:
what I suggested does not work and I think even jjafferr suggestion doesn't work either.:
try this:
get all non null: DCount("[CostAllocationCd]","ExpensesItems","([CostAllocationCd]) LIKE '*'")

then do the same thing but on a field that you are sure has no nulls, like the primary key field...then do the substarction
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjafferrCommented:
D. Carter

Did any of the above work for you?

jaffer
0
 
GRayLCommented:
Simply put, DCount() will not count a null expression.

Try:

=DCount("nz[CostAllocationCd],0),","ExpensesItems","nz([CostAllocationCD],0)=0")

This assumes there are nulls and always some value other than zero for this field.  Otherwise you have to use some value that you know does not exist in any of the records for that field, such as 0.0001, in both locations above.  
0
 
GRayLCommented:
For example:

=DCount("nz[CostAllocationCd],0.001),","ExpensesItems","nz([CostAllocationCD],0.001)=0.001")

0
 
GRayLCommented:
I am assuming CostAllocationCd is a number and not text.  It it is a text field, invent your own text for the null value.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now