SQL and is null question

[John.Woody]

I have the following sql statement.

strSQL = "SELECT RevEntry.OperatorID, RevEntry.RevTypeID, RevEntry.CampaignID, RevEntry.HolidayProgramID, " _
& "RevEntry_Sub.MemberID, Sum(RevEntry_Sub.Pax) AS SumOfPax, Sum(RevEntry_Sub.Bookings) AS SumOfBookings, " _
& "Sum(RevEntry_Sub.Revenue) AS SumOfRevenue " _
& "FROM RevEntry INNER JOIN RevEntry_Sub ON RevEntry.RevEntryID = RevEntry_Sub.RevEntryID " _
& "WHERE (((RevEntry.StartDate) >= #" & [Forms]![F_RevEntry]![StartDate] & "#) And " _
& "((RevEntry.EndDate) <= #" & [Forms]![F_RevEntry]![EndDate] & "#)) " _
& "GROUP BY RevEntry.OperatorID, RevEntry.RevTypeID, RevEntry.CampaignID, RevEntry.HolidayProgramID, " _
& "RevEntry_Sub.MemberID " _
& "HAVING (((RevEntry.OperatorID)= " & [Forms]![F_RevEntry]![OperatorID] & ") AND ((RevEntry.RevTypeID)= " _
& [Forms]![F_RevEntry]![RevTypeID] & " Or (RevEntry.RevTypeID) Is Null) AND ((RevEntry.CampaignID)= " _
& [Forms]![F_RevEntry]![CampaignID] & " Or (RevEntry.CampaignID) Is Null) AND ((RevEntry.HolidayProgramID)= " _
& [Forms]![F_RevEntry]![HolidayProgramID] & " Or (RevEntry.HolidayProgramID) Is Null) AND ((RevEntry_Sub.MemberID)= " _
& [Forms]![F_RevEntry]![F_RevEntry_Sub].[Form]![MemberID] & "));"


If the CampaignID or HolidayProgramID = Null then I get the error 3075 Syntax Error records do not match. If I change the values in the fields from Null to 0 and ammend the SQL to

"(RevEntry.HolidayProgramID) = 0)"

It works.

What is wrong with the origional SQL? I would rather keep the values as null if I can.

THanks
John

 

[Rich]

Would suspect that the Nz function would cure it

 

[neileg]

I think you always get a probelm why you try to match a null value to a null value, unless you are using an Is Null test.

I agree with Rich that Nz() should do it. You can have a valid test for 0=0

 

[John.Woody]

Excellent Thank you NZ works. Now why didn't I think of that