Updating a control with DSum & getting "Invalid use of Null"

[jlathem]

Hey guys,

I am getting an error, “Invalid use of Null” in the chkSum line. I get MsgBox "After SQL 1 Runs" then the error. I do not get the second MsgBox "After chkSum"!

I checked the IPP_Tracking table and neither the Calc_Pmt_Amount field nor the Date_Batched field has a Null record, at all.

I am at a loss with this one. Anyone have any suggestions?

Thanks in advance for any assistance you can offer.

James

[SIZE=3][FONT=Times New Roman]          'Declare Variables[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Dim chkSum As Currency[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]'MsgBox is for testing only[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]MsgBox "After SQL 1 Runs"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]              'Sum the Calc_Pmt_Amount to put in the Batch table[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]              chkSum = DSum("Calc_Pmt_Amount", "IPP_Tracking", "Date_Batched = #" & Format(Date, "d/m/yyyy") & "#")[/B][/COLOR][/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]'MsgBox is for testing only[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]MsgBox "After chkSum"[/FONT][/SIZE]

 

[pbaldy]

Is it possible that no records meet the DSum criteria? It will return a Null in that event, which your variable can't handle. You can either declare that as a Variant or surround the DSum with an Nz function.

 

[jlathem]

Is it possible that no records meet the DSum criteria? It will return a Null in that event, which your variable can't handle. You can either declare that as a Variant or surround the DSum with an Nz function.

Hi Paul,

I used the Nz function and it helped me to figure out what I was doing.

I have to hand my head in shame on this one. I had the wrong date format. I have been using m/d/yyyy and in the code I had a typo of d/m/yyyy.

Shame,. Shame on me!

I been coding for 15 hours but I am still ashamed of this one.

Thank you Paul for helping me work through this one.

James

 

[pbaldy]

No problem, James. Glad you got it sorted out.

 

[jlathem]

Hey Paul,

By chance do you have a few moments to look at another piece of code that was in that piece you looked at earlier?

This piece was a few lines below what you helped me with.

If not I can always post to the group.

James

 

[pbaldy]

Sure, I'll be online for a bit longer.

 

[jlathem]

Hey Paul,

Nevermind, I got it worked out.

Thanks again for all your help.

James