Dealing with Null value on SQL Statement

[VSolano]

I tested the nz(RS2!CompanyID,0) and nz(RS2!CompanyID, " ") and I received the following errors

Error 94 invalid use of null (debug.print one) and 3075 missing operator with debug.print two

plancarrier=1 And entityid=2 And benefitsID=1 AND plantype=7 AND plandatailid=60 AND year(invoicedate)=2016 AND month(invoicedate)=2 AND CompanyID=0
plancarrier=1 And entityid=2 And benefitsID=1 AND plantype=7 AND plandatailid=60 AND year(invoicedate)=2016 AND month(invoicedate)=2 AND CompanyID=

 

[June7]

I expect no record would match with CompanyID=0 unless you really have a record with that ID.

The invalid error is on the expression that uses the result of DSum. DSum returns Null when there is no match. SumInvoiceAmt is declared as a Double variable. Only Variant can hold null.

Don't you want a result even if no parameter provided for CompanyID?

Build the criteria incrementally with If conditionals. If field is Null, don't include it in the criteria.

If you don't want If Then conditionals, try an IIf().

& IIf(IsNull(rst!companyid), "", " AND CompanyID = " & rst!companyid)

 

[theDBguy]

Hi. Actually, what June is saying makes sense. Why don't you also Debug.Print the result of DSum() before assigning it to see what you're getting?

 

[Gasman]

I expect no record would match with CompanyID=0 unless you really have a record with that ID.

June7,
I've already queried that and got no response.?

 

[theDBguy]

I tested the nz(RS2!CompanyID,0) and nz(RS2!CompanyID, " ") and I received the following errors

Error 94 invalid use of null (debug.print one) and 3075 missing operator with debug.print two

plancarrier=1 And entityid=2 And benefitsID=1 AND plantype=7 AND plandatailid=60 AND year(invoicedate)=2016 AND month(invoicedate)=2 AND CompanyID=0
plancarrier=1 And entityid=2 And benefitsID=1 AND plantype=7 AND plandatailid=60 AND year(invoicedate)=2016 AND month(invoicedate)=2 AND CompanyID=

Hi. What happens if you try it this way now?

Dim strCriteria As String 
strCriteria = "plancarrier=" & RS2!PlanCarrier & " And entityid=" & RS2!EntityID & " And benefitsID=" & RS2!benefitsid & " AND plantype=" & RS2!plantype & " AND plandatailid=" & rst!plandatailid & " AND year(invoicedate)=" & InvYr & " AND month(invoicedate)=" & month(rst!InvoiceDate) & " AND CompanyID=" & Nz(rst!companyid,0) 
Debug.Print strCriteria 
SumInvoiceAmt = Nz(DSum("invoiceamount", "tbbilling", strCriteria),0)