DCount works but DSum doesn't

[Magster]

Good morning!

I am struggling with this - so I hope someone can help!

Mycode works great when I use the DCount function, but fails when I change it to DSum which errors with: Error 94 - invalid use of null.

dblCntr = DSum("[intEventCount]", "tblResourceEvents", "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = " & Me.cbo1)

The entire table has valid data - no nulls. myKey and Me.cbo1 both have correct values.

Any ideas?

Thanks

 

[pr2-eugin]

DSum, unlike DCount would return a Null value if the criteria is not met.. So when you try to assign it a variable that is not Variant type it will throw the Invalid Use of Null error, two ways to over come..

dblCntr = [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"][COLOR=Red][B]Nz([/B][/COLOR][/URL]DSum("[intEventCount]", "tblResourceEvents",  "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = "  & Me.cbo1)[COLOR=Red][B], 0)[/B][/COLOR]

Or

Dim dblCntr As Variant
dblCntr = DSum("[intEventCount]", "tblResourceEvents",  "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = "  & Me.cbo1)
If IsNull(dblCntr) Then MsgBox "Nothing to see"

I prefer the first method.. i.e. use of Nz..

 

[Magster]

Thanks Paul - I totally forgot about the nz() function. I was too focused on what I was sending into the function rather than what was returning to me.

Again - I truly appreciate the help - and so quickly!