Dealing with Null value on SQL Statement (1 Viewer)

VSolano

Registered User.
Local time
Today, 03:13
Joined
Feb 21, 2017
Messages
85
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

Code:
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

AWF VIP
Local time
Yesterday, 23:13
Joined
Mar 9, 2014
Messages
5,424
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)
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 00:13
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
14,050
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’m here to help
Staff member
Local time
Today, 00:13
Joined
Oct 29, 2018
Messages
21,358
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

Code:
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?
Code:
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)
 

Users who are viewing this thread

Top Bottom