Solved

DSum cancelling on Error 94, Invalid use of Nulls

Posted on 2011-02-17
3
817 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

691 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