Dealing with Null value on SQL Statement

[VSolano]

Hello All.

I am breaking my head trying to find the way of handling this null value on the following expression.

when I add the companyid parameter will give me syntax error 3075 of missing operator and I ran out of options base on my limited knowledge of vba.

The code breaks when I add :

" AND CompanyID = " & Nz(rst!companyid, "")) to the aggregate function.

CompanyID has some valid null or empty value

Any help will be greatly appreciate.

Public Function ColumnarReport()
    On Error GoTo ErrorHandle
    Set db = CurrentDb
    Dim SQL As String
    Dim RS2 As Recordset
    Dim InvYr As Integer
    InvYr = 2016
    
    DelTbl ("tbcolumnreport")
    Set tdef = db.CreateTableDef("tbcolumnreport")
    tdef.Fields.Append tdef.CreateField("PlanCarrier", dbInteger)
    tdef.Fields.Append tdef.CreateField("EntityID", dbInteger)
    tdef.Fields.Append tdef.CreateField("CompanyID", dbInteger)
    tdef.Fields.Append tdef.CreateField("Benefitsid", dbInteger)
    tdef.Fields.Append tdef.CreateField("Plantype", dbInteger)
    tdef.Fields.Append tdef.CreateField("plandatailid", dbInteger)
    tdef.Fields.Append tdef.CreateField("January", dbDouble)
    tdef.Fields.Append tdef.CreateField("February", dbDouble)
    tdef.Fields.Append tdef.CreateField("March", dbDouble)
    tdef.Fields.Append tdef.CreateField("April", dbDouble)
    tdef.Fields.Append tdef.CreateField("May", dbDouble)
    tdef.Fields.Append tdef.CreateField("June", dbDouble)
    tdef.Fields.Append tdef.CreateField("July", dbDouble)
    tdef.Fields.Append tdef.CreateField("August", dbDouble)
    tdef.Fields.Append tdef.CreateField("September", dbDouble)
    tdef.Fields.Append tdef.CreateField("October", dbDouble)
    tdef.Fields.Append tdef.CreateField("November", dbDouble)
    tdef.Fields.Append tdef.CreateField("December", dbDouble)
    Dim STSearch As String
    Dim RpMonth As String
    Dim SumInvoiceAmt As Double
    Dim InvoiceDate As Date
    Dim CoCodeID As Integer
    Dim CoCodeID2 As Integer
    
    
    db.TableDefs.Append tdef
    db.TableDefs.Refresh
    Set rst = db.OpenRecordset("tbbilling", dbOpenDynaset, dbSeeChanges)
    Set RS2 = db.OpenRecordset("tbcolumnreport", dbOpenDynaset, dbSeeChanges)
'    CoCodeID = Nz(rst!companyid)
'    CoCodeID2 = Nz(RS2!companyid)
 
    SQL = "INSERT INTO tbcolumnreport ( PlanCarrier, EntityID, CompanyID, benefitsid, PlanType, plandatailid )" _
            & " SELECT DISTINCT tbbilling.PlanCarrier, tbbilling.EntityID, tbbilling.companyid, tbbilling.benefitsid, tbbilling.PlanType, tbbilling.plandatailid " _
            & " FROM tbbilling " _
            & " WHERE year(tbbilling.invoicedate)= " & InvYr & ";"
         
                
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    RS2.Requery
    
    Do While rst.EOF = False
    
        If Year(rst!InvoiceDate) = InvYr Then

        
                STSearch = rst!PlanCarrier & rst!EntityID & rst!companyid & rst!benefitsid & rst!plantype & rst!plandatailid
    
               
'                    Debug.Print rst.RecordCount
'                    Debug.Print RS2.RecordCount
                        ' I need to search RS2 to see if the value is there
                        
                
                RS2.FindFirst "[PlanCarrier] & [EntityID] & [companyid] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"
              
               
                
                                        
                If RS2.NoMatch = False Then
                
                      InvoiceDate = rst!InvoiceDate
                    
                    Select Case month(rst!InvoiceDate)
                      
                    
                    
       
                        Case 1
                            SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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, ""))
                            Debug.Print SumInvoiceAmt
                            RS2.Edit
                            RS2!january = SumInvoiceAmt
                            RS2.Update

 

[theDBguy]

Hi. Is CompanyID a numeric field? If so, try using 0 instead of "" in your Nz() function.

 

[VSolano]

I tried that and it does not work

 

[theDBguy]

I tried that and it does not work

Okay, what does "does not work" mean? Also, can you point out exactly where in the code the error was happening? Thanks.

 

[VSolano]

When I used Zero it give invalid use of null

 

[theDBguy]

When I used Zero it give invalid use of null

Hi. That sounds interesting. Could you please show us exactly how you tried to use Zero (0) instead of ZLS ("")? Thanks.

 

[VSolano]

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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))

 

[theDBguy]

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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))

Okay, a few questions... What are the data types of the following fields in tbbilling?


plancarrier
plantype


And also, what happens if you don't use the Nz() function at all? Right now, you're only using it for CompanyID. I would say either use it for all fields or none at all, just to see what happens.

 

[VSolano]

the tbbilling is just a table with the data
companyid is the only one with some null value

 

[theDBguy]

the tbbilling is just a table with the data
companyid is the only one with some null value

Hi. What about my questions? Since I can't see your db, I have to ask what may seem like obvious questions to you but will provide some clarity for me and help you figure out what's happening.

 

[VSolano]

Okay, a few questions... What are the data types of the following fields in tbbilling?


plancarrier
plantype


And also, what happens if you don't use the Nz() function at all? Right now, you're only using it for CompanyID. I would say either use it for all fields or none at all, just to see what happens.

these data type is integer

 

[theDBguy]

Hi. Thanks. It's bed time over here, so I'll see you tomorrow. In the meantime, I would suggest storing your criteria into a variable, so you can examine what the DSum() is actually seeing. Good night.

 

[VSolano]

Hi. Thanks. It's bed time over here, so I'll see you tomorrow. In the meantime, I would suggest storing your criteria into a variable, so you can examine what the DSum() is actually seeing. Good night.

I also tried to store the value on a variable and it was unsuccessfully.

 

[Galaxiom]

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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))

Firstly, ask yourself why are the Month() and Year() functions are not capitalised. If you pasted actual code from the VBA editor they should have been. Perhaps you are posting the code you think you have.

Is invoicedate a datetime field? Perhaps it has a Null. That would give the error.

Aside from that:

Are you sure this report could not be done with a CrossTab query? I have not looked at it closely this kind of thing is usually done that way.

Your code is very inefficient for multiple reasons.

The SQL applies two functions to every record in the table before it can select. You should be comparing the invoicedate to a date range constructed from InvYr. (Read up on the meaning of SargABLE queries.)

You are running a Dsum for every record. This is like running a query each time. Try to retrieve the date from a single query even it if requires subqueries.

FindFirst in a recordset is very slow. It is actually faster to get straight to the record by requerying multiple times with new criteria.

 

[Gasman]

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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))

How are you meant to find any record with a company id of zero?

 

[VSolano]

Firstly, ask yourself why are the Month() and Year() functions are not capitalised. If you pasted actual code from the VBA editor they should have been. Perhaps you are posting the code you think you have.

Is invoicedate a datetime field? Perhaps it has a Null. That would give the error.

Aside from that:

Are you sure this report could not be done with a CrossTab query? I have not looked at it closely this kind of thing is usually done that way.

Your code is very inefficient for multiple reasons.

The SQL applies two functions to every record in the table before it can select. You should be comparing the invoicedate to a date range constructed from InvYr. (Read up on the meaning of SargABLE queries.)

You are running a Dsum for every record. This is like running a query each time. Try to retrieve the date from a single query even it if requires subqueries.

FindFirst in a recordset is very slow. It is actually faster to get straight to the record by requerying multiple times with new criteria.

The code works fine without the addition of the companyid

 

[theDBguy]

I also tried to store the value on a variable and it was unsuccessfully.

Hi. Good morning. Again, please remember we can't see what you're looking at or what's happening with your db, so you'll have to describe it with more details for us. So, what does "unsuccessfully" mean? Can you show us how you assigned the criteria to a variable and also show us the result? Did you step through the code and examined the content of the variable? Or, did you use Debug.Print? My suggestion to use a variable wasn't a recommendation to "fix" your problem. It was a way to help you "find" the cause of the problem, so we can fix it. Cheers!

 

[VSolano]

Okay, what does "does not work" mean? Also, can you point out exactly where in the code the error was happening? Thanks.

When I run the code it gave me error 3075 Missing Operator

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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 = " & rst!companyid)

 

[VSolano]

Hi. Good morning. Again, please remember we can't see what you're looking at or what's happening with your db, so you'll have to describe it with more details for us. So, what does "unsuccessfully" mean? Can you show us how you assigned the criteria to a variable and also show us the result? Did you step through the code and examined the content of the variable? Or, did you use Debug.Print? My suggestion to use a variable wasn't a recommendation to "fix" your problem. It was a way to help you "find" the cause of the problem, so we can fix it. Cheers!

I created the following two variable

Dim CoCodeID2 As Integer

CoCodeID = Nz(rst!companyid)
CoCodeID2 = Nz(RS2!companyid)

I used as following and I received error -2147352567

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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 = " & CoCodeID2)

 

[theDBguy]

I created the following two variable

Dim CoCodeID2 As Integer

CoCodeID = Nz(rst!companyid)
CoCodeID2 = Nz(RS2!companyid)

I used as following and I received error -2147352567

SumInvoiceAmt = DSum("invoiceamount", "tbbilling", " 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 = " & CoCodeID2)

Hi. I guess I wasn't clear. Here's what I was suggesting. Create a String variable and assign the DSum() criteria to it. And then, do a Debug.Print and show us what you get. So, something like this:

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 = DSum("invoiceamount", "tbbilling", strCriteria)

Again, I don't expect the error to go away. I am just trying to understand why we're getting an error in the first place.