Results 1 to 4 of 4
  1. #1
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7

    Invalid use of Null in Module error

    Hi,

    I have an access database that uses a module to split rows in a table into 1 or more rows in another table following rules regarding 2 date fields which works - however if a field is blank in the source table it errors out.

    I need the module to function even if a field in the source table is blank as their will be fields in my data table that are not always required (eg PO Number) so they will be blank - is there a way to fix this?



    It returns a run time error 94 'Invalid use of null' when i execute the macro.

    Test Database.accdb

    Cheers,
    Tom

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make allowances for nulls in the code....

    If IsNull(field) then
    'do this
    else
    'do this
    endif

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The first 2 lines in EVERY code page (which you don't have) should be
    Code:
    Option Compare Database
    Option Explicit
    These help keep you from declaring a variable, but using a different variable name that has not been declared.
    For instance, you have declared
    Code:
        Dim InvoiceNo As String
    , but in code you have
    Code:
            InvoiceNum = rst![Invoice Number]
    and used
    Code:
    & vat & "', '" & InvoiceNum & "', '"
    in the SQL string.

    ================================================== =======

    This code executes for me. Changes are in BLUE:
    Code:
    Option Compare Database
    Option Explicit
    
    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   'InvoiceNum
    
        '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]
            If IsNull(rst![Invoice Number]) Then
                InvoiceNo = vbNullString
            Else
                InvoiceNo = rst![Invoice Number]
            End If
            
            If IsNull(rst![Number of Installment Invoices]) Then
                NumOfInstalments = vbNullString
            Else
                NumOfInstalments = rst![Number of Installment Invoices]
            End If
            
            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 & "', '" & InvoiceNo & "', '" & NumOfInstalments & "', '" & InstallmentPostingDate & "', '" & Installment1 & "', '" & InstalmentDD1 & "')"
                '           Run SQL
                'MsgBox strSQL
                '            DoCmd.SetWarnings False
                '            DoCmd.RunSQL strSQL
                '            DoCmd.SetWarnings True
                db.Execute strSQL, dbFailOnError
    
                '           Increment dates for next round
                wStartDt = BOMDate(wEndDt + 1)
                wEndDt = EOMDate(wStartDt)
    
            Loop Until lastOne = True
    
            '       Move to next record
            rst.MoveNext
    
        Loop
    
        '   Clean up
        rst.Close  '   Close recordset
        Set rst = Nothing
        Set db = Nothing
        MsgBox "Done"
    End Function

    Object names should be letters and numbers.
    NO spaces, punctuation or special characters (exception is the underscore) in object names
    Do not begin an object name with a number.


    Be aware that "DATA" is a SQL reserved word and shouldn't be used as an object name.



    Good luck with your project.......

  4. #4
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7
    Hi Both,

    Thanks for the replies - I managed to fix this.

    Cheers,
    Tom

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Invalid use of NULL error
    By CHEECO in forum Access
    Replies: 13
    Last Post: 03-16-2016, 07:20 PM
  2. Run time error '94': Invalid Use of Null... help
    By batowl in forum Programming
    Replies: 3
    Last Post: 05-14-2014, 01:58 PM
  3. Replies: 1
    Last Post: 03-22-2013, 09:59 AM
  4. Error 94: Invalid Use of Null
    By athomas8251 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 11:46 AM
  5. DLookUp function giving invalid use of null error
    By shubhamgandhi in forum Programming
    Replies: 4
    Last Post: 07-21-2011, 06:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums