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