SQL Statement to find records with a Null date?

[IncidentalProgrammer]

Hi, all!



I'm fighting with SQL statements again...and they're winning.

My boss wanted some different data displayed, and now I need to be able to find records where a certain date field is null, meaning that a claim is still open. I've searched online, and seen all kinds of different ways people are saying to do it. The bulk of the SQL statement that I had before, that searches for dates within a range, will work for this, it's just finding those without dates that's tricky. Per the things I've read online, I've tried NULL, IS NULL, and Nz, and on both sides of the date field, but nothing works; when I run it, the debug.print looks right, but the text box gets a "0", every time, even though I've got test data that SHOULD be putting 2,000 in it.

Can anyone tell where the NULL line is going wrong?

     "SELECT Sum(tblMnlClaim.MClmRes) AS SumOfMClmRes " _
     & " FROM tblMnlClaim WHERE tblMnlClaim.EntID = '" & Me.ctlActEntID & "' " _
     & " AND tblMnlClaim.PolNum = '" & Me.ctltblRnwlTrack_PolNum & "'" _
     & " AND tblMnlClaim.MClmCxDt IS NULL;"

[June7]

I don't see anything wrong with the SQL syntax.

This SQL is constructed in VBA then what do you do with it?

[IncidentalProgrammer]

Thanks, June7!

I'm feeding this into unbound fields on my form. I'm doing two different sets of SQL statements for each: one for dates that fall within the range, and the other for those without close dates (open claims). I'm using this under each to put them into the form:

     Set db = CurrentDb
     Set rs = db.OpenRecordset(L36RSQL)
     Me.ctl36OR = rs!SumOfMClmRes

And from there, I'll use VBA to add the proper sets, and place them in the bound fields. (I know I've read that you can do math with queries, but as shaky as my grasp of SQL is, I feel more comfortable doing it in VBA.)

[IncidentalProgrammer]

Is it possible that that date field isn't truly Null? I have it formatted as Date.

[IncidentalProgrammer]

Well, now I feel silly. While glaring at these SQL statements, I realized that there was one letter out of whack, and that's what threw them off. They work now.