?
Solved

DSum cancelling on Error 94, Invalid use of Nulls

Posted on 2011-02-17
3
Medium Priority
?
825 Views
Last Modified: 2012-05-11
I am using the following stetment to get a total number of patients in my table.

AllPatients = DSum("[Patients]", wkTable, wkSumClientWhere)

When there are no patients matching the criterion it cancels with an error 94, invalid use of nulls.  It works without fail if there is/are matching patient(s).

How can I set this up to trap that particular error and have the value of AllPatients be zero if there are no matches in the table.
 

0
Comment
Question by:mlcktmguy
  • 2
3 Comments
 
LVL 75
ID: 34920959
Where is the definition of wkSumClientWhere   and wkTable ... show that part of the code.

mx
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 34921041
I used a debug to display the contents of the two variable when this statement cancelled and when the Dsum was successful.  The contents were:

wkTable=  tbl_365Day_Counts_Final
 
wkSumClientWhere =  ( ([ClientID]  IN (Select[ClientID] from tblWkRptClients))  And ([LinkDate] Between #3/1/1930# and #3/3/2010#) )

The format of the statements is OK the issue only occurs when there is no matching criterion in the program.

For now I've set it up like this:

AllPatients =0
on error resume next
AllPatients = DSum("[Patients]", wkTable, wkSumClientWhere)
on error go to 0

It works and the end result is a zero in AllPatients if no patients match the criterion.  I just thought there might be a better more general way of handling it.


0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 34921226
Interesting WHERE clause for DSum() ... sub query.  Never tried that.   But if you have it working ... guess I would go with that.

mx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question