Invalid Use Of Null

[andycambo via AccessMonster.com]

Hi,

I know what this expression means, basically nothing is there, but I'm not
quite sure how to get around it.

Basically, I have some code that costs depending on what type of Funding
Method is used. Each Funding Method has different calculations for the times.
Here is some code to make it clearer.

First I get the total times for each funding method:-

atotalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tFundingMethod =
'" & "A" & "' AND tMatterID=" & theFileNumber)

btotalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tFundingMethod =
'" & "B" & "' AND tMatterID=" & theFileNumber)

ctotalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tFundingMethod =
'" & "C" & "' AND tMatterID=" & theFileNumber)

Then I calculate the Costs:

atotalTravelTimeN = atotalTravelTime * 0.4125
btotalTravelTimeN = btotalTravelTime * 0.4125
ctotalTravelTimeN = ctotalTravelTime * 0.2083333

etc.....

My problem is, when the code is executed if there aren't any times entered in
any of the tTravelTime under the Funding Method A, B or C then I get the
'Invalid Use of Null' message. If there are times entered under Funding
Method A but not B then the code stops once it tries to bTotalTravelTimes for
'B' etc

I've used similar code elsewhere and haven't had any problems so I can't see
what is causing this error.

Any help is appreciated.

Thanks,
Andy.

 

[John Spencer]

It kind of depends on how you want to handle the problem. One method would be
to use the NZ function to return zero if there are no associated records

atotalTravelTime = NZ(DSum("[tTravelTime]", "tblTimeRecord", "tFundingMethod =
'" & "A" & "' AND tMatterID=" & theFileNumber),0)

This would make the calculation
atotalTravelTimeN = atotalTravelTime * 0.4125
use Zero and return zero.

Another method would be to test aTotalTravelTime and see if it is null and
then assign whatever value you wish based on that fact.

If IsNull(aTotalTravelTime) Then
atotalTravelTimeN = Null
'or if you want zero instead of null
' atotalTravelTimeN = 0
Else
atotalTravelTimeN = atotalTravelTime * 0.4125
End if

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[andycambo via AccessMonster.com]

It kind of depends on how you want to handle the problem. One method would be
to use the NZ function to return zero if there are no associated records

NZ is most probably my best way to do this. I've used NZ before for a string
but completly forgot about it.

Thanks for your quick response

Andy..