VBA Code error on blank values (runtime 94)

[Tom123456]

Hi,

I have a piece of VBA code that will take the data from my source table and split the rows into my destination table based on rules regarding 2 date fields in the source table.

However if any field is empty in the source table it errors out and i need the code to work when values are empty as there will be times when it is.

Public Function SplitData()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Dim ref As String
    Dim lineId As Long
    Dim sales As String
    Dim customer As String
    Dim startDt As Date
    Dim endDt As Date
    Dim priCon As String
    Dim brFam As String
    Dim prdPlat As String
    Dim prdType As String
    Dim prdName As String
    Dim units As String
    Dim amount As Currency
    Dim conMonth As String
    Dim custConDt As Date
    Dim amtCur As String
    Dim navID As String
    Dim vat As String
    Dim InvoiceNo As String
    
    'Define Instalments information for 2nd dataset loop once date split is in dest Table
    
    Dim NumOfInstalments As String
    Dim InstallmentPostingDate As String
    Dim Installment1 As Currency
    Dim InstalmentDD1 As Date
    
    
    Dim wStartDt As Date
    Dim wEndDt As Date
    Dim nStartDt As Date
    Dim nEndDt As Date
    Dim nAmount As Currency
    Dim lastOne As Boolean
    Dim totDays As Long
    Dim monDays As Long
    Dim monAmt As Currency
    Dim runAmt As Currency

    
    Dim strSQL As String
    
'   Open up query in recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qrySourceTable", dbOpenDynaset)    'enter your query name
    
'   Loop through recordset
    rst.MoveFirst
    Do While Not rst.EOF
'       Capture record values
        ref = rst!Reference
        lineId = rst![Line ID]
        sales = rst![Sales Person]
        customer = rst!customer
        startDt = rst![Start Date]
        endDt = rst![End Date]
        priCon = rst![Primary Contact]
        brFam = rst![Brand Family]
        prdPlat = rst![Product Platform]
        prdType = rst![Product Type]
        prdName = rst![Product Name]
        units = rst![Unit of Measure]
        amount = rst!amount
        conMonth = rst![Contract Month]
        custConDt = rst![Customer Contract Date]
        amtCur = rst![Amount Currency]
        navID = rst![Navision ID]
        vat = rst![VAT Rate]
        InvoiceNum = rst![Invoice Number]
        NumOfInstalments = rst![Number of Installment Invoices]
        InstallmentPostingDate = rst![Installment Posting Date]
        Installment1 = rst![Instalment 1]
        InstalmentDD1 = rst![Instalment 1 Due Date]

    
'       Initialize counter
        lastOne = False
        wStartDt = startDt
        wEndDt = EOMDate(startDt)
        runAmt = 0
        
'       Capture total number of days
        totDays = endDt - startDt + 1
        
'       Loop through records
        Do
        
'           Check to see if end date is after end of month, and set dates
            If endDt > wEndDt Then
                nStartDt = wStartDt
                nEndDt = wEndDt
            Else
                nStartDt = wStartDt
                nEndDt = endDt
                lastOne = True
            End If
            
'           Calculate monthly days & monthly amount
            monDays = nEndDt - nStartDt + 1
'           Calculate monthly amount and running amount
            If lastOne Then
                nAmount = amount - runAmt
            Else
                nAmount = Round(amount * monDays / totDays, 2)
                runAmt = runAmt + nAmount
            End If
            
'           Build SQL query to insert new record
            strSQL = "INSERT INTO DestTable ( Reference, [Line ID], [Sales Person], Customer, [Start Date], [End Date], [Primary Contact], [Brand Family], [Product Platform], "
            strSQL = strSQL & "[Product Type], [Product Name], [Unit of Measure], Amount, [Contract Month], [Customer Contract Date], [Amount Currency], [Navision ID],[VAT Rate],[Invoice Number],[Number of Installment Invoices],[Installment Posting Date],[Instalment 1],[Instalment 1 Due Date]) "
            strSQL = strSQL & "VALUES ('" & ref & "', " & lineId & ", '" & sales & "', '" & customer & "', #" & Format(nStartDt, "dd-mmm-yyyy") & "#, #" & Format(nEndDt, "dd-mmm-yyyy") & "#, '"
            strSQL = strSQL & priCon & "', '" & brFam & "', '" & prdPlat & "', '" & prdType & "', '" & prdName & "', '" & units & "', " & nAmount & ", '"
            strSQL = strSQL & conMonth & "', #" & Format(custConDt, "dd-mmm-yyyy") & "#, '" & amtCur & "', '" & navID & "', '" & vat & "', '" & InvoiceNum & "', '" & NumOfInstalments & "', '" & InstallmentPostingDate & "', '" & Installment1 & "', '" & InstalmentDD1 & "')"
'           Run SQL
            'MsgBox strSQL
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
          
'           Increment dates for next round
            wStartDt = BOMDate(wEndDt + 1)
            wEndDt = EOMDate(wStartDt)
        
        Loop Until lastOne = True
        
'       Move to next record
        rst.MoveNext
        
    Loop
    
'   Close recordset
    rst.Close
    
End Function


Function BOMDate(inputDate) As Date
'   Returns the beginning of month date for any inputted date
    BOMDate = DateSerial(Year(inputDate), Month(inputDate), 1)
End Function


Function EOMDate(inputDate) As Date
'   Returns the end of month date for any inputted date
    EOMDate = DateSerial(Year(inputDate), Month(inputDate) + 1, 0)
End Function

I receive a 'Run-time error '94' invalid use of null when i try to execute the code.


Can this code be changed to accept blank values from the 'data' table?

Cheers,
Tom

[OBP]

The simple answer ys yes, however you are conducting so many data field transfers it is going to take some major re-programming.
It would be simpler to ensure that there are no Null values in the table.
ie ensure the default for Numeric fields is Zero and for Text fields something like "-", thst would only leave date fields to contend with.
Currently you need to add an "is Null" test to every field, this could incorporate the NZ function as well, in fact you could create a query with the NZ functions in and use that as the recordset.
The standard test looks like this
If not is null(rst!Reference) then ref = rst!Reference

You could also give the object field a value like this
If not is null(rst!Reference) then
ref = rst!Reference
else
ref = "0"' or something meaningful
end if

[Tom123456]

Hi OBP,

Thank you for the reply - i managed to fix this with the NZ function.

Cheers,
Tom