DSum cancelling on Error 94, Invalid use of Nulls
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mx