Solved

Access Error 94

Posted on 2011-09-29
26
229 Views
Last Modified: 2012-05-12
Hi,

I keep getting a Run Time Error 94 Invalid use of Null. Could it be something worng with code?

It has only started after I entered the code:

Private Sub txtEnd_AfterUpdate()
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
End Sub

Private Sub txtStart_AfterUpdate()
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
End Sub

Should I add a null reference after txtStart?





0
Comment
Question by:SerinaStar
  • 9
  • 8
  • 8
  • +1
26 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you post this function

TimeDuration

?
0
 

Author Comment

by:SerinaStar
Comment Utility
Please see the code below. The form will preform the function after I click end on the Run time Error.
Option Compare Database
Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
    
    
    
    
    
   
End Function

Open in new window

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
OK ... what line of code is the error occurring on?

One thing ... since the Function is defined AS String ...  In the two lines at the bottom where you have

TimeDuration =  ......
If the expression on the right happens to be Null, you will get that error because a String cannot be Null.

mx
0
 

Author Comment

by:SerinaStar
Comment Utility
it is occuring on Ln8 of the below:

Private Sub txtEnd_AfterUpdate()
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
End Sub

Private Sub txtStart_AfterUpdate()
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
End Sub
Which is an expression used to update the table with the Shift duration result.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Ln8 of the below ?

Try changing this

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String

to this:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As Variant


Also ... this:

strHours = Format(dtmTime, "hh")

If the is any chance that dtmTime can be passed as Null, then this line will generate Error 94 because you strHours Dimmed as String.

IF *anything* dimmed as String can be Null, you will get that error.

0
 

Author Comment

by:SerinaStar
Comment Utility
Ok I have updated the function TimeDuration to as variant as suggested however it is still showing up.

When I hit Debug it highlights the below yellow:

Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])

I have used this as a Event Afterupdate on the form properities for both the txtstart and txtend fields.

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Pretty sure that still indicates the issue is in the Function for the reasons I mentioned. There is nothing inherently wrong with that line of code. Pretty sure some 'string' variable is trying to be set to Null.


Can you upload the db and indicate how to reproduce the error?

I'm off to bed now ... can pick up in the am.

mx
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Returning a string from the function should work for you.  The function return will either default to the empty string or it will return the string values you are defining (the equations you have will always return a non-null value).

Your thought about adding a null reference after txtStart is a good one... and txtEnd too.  If either of those are null, your function will give you errors.  You should define the corresponding parameters in your function statement as Variants and add code to handle nulls.


Option Compare Database
' ***** Change date parameters to Variants  *****
Public Function TimeDuration(dtmFrom As Variant, dtmTo As Variant, _
            Optional blnShowdays As Boolean = False) As String

'****** Add handling for null parameters
        if IsDate(dtmFrom) = False or isDate(dtmTo) = False Then
                    '*** one or both parameters is Null or some other non-date
                    ' do something, or just exit the function
                    Exit Function
        End if
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
End Function 

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Explaining my inline comment in the null handler in the code "' do something, or just exit the function"...

Depending on how the function is being used, you can add informational messageboxes letting the user know that nulls were passed to the function or have the function return a string such as "No result" - or you can do nothing, and simply exit the function.  If you simply exit the function, the default value returned is the empty string (""), and that can be handled as needed by whatever code, etc calls the TimeDuration function.

The point of that added code, however is to prevent null values from being processed by the function, which will result in Error 94 - Invalid use of Null.

(If interested, you can see exactly where your original code failed by calling it from the immediate window, passing it nulls instead of actual dates)
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
test both textboxes for input before calling the calcualtion


Private Sub txtEnd_AfterUpdate()
if me.txtStart & ""<> "" then
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
else
msgbox "Enter start time"
me.txtstart.setfocus
end if

End Sub

Private Sub txtStart_AfterUpdate()
if me.txtEnd & ""<> "" then
Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])

else
'msgbox "Enter End time"
me.txtEnd.setfocus
end if

End Sub
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility


SerinaStar:
The specific reason the Error 94 is on that line is easy to reproduce (I just did so) because ... when you end a value in txtStart and hit enter or tab ... to txtEnd ... the Function is called, but txtEnd is Null at that point, and you are trying to pass as a Date:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String

and that causes the Error 94 directly on that line, although there is nothing wrong with the line per se. Just that the Function cannot accept that Null txtEnd value.  

So ... if you make the following changes, you can leave your original Function as is:

Private Sub txtStart_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtStart) Then
        MsgBox "Enter valid Start date"
        Cancel = True
    End If
End Sub

Private Sub txtStart_AfterUpdate()
    If IsDate(Me.txtStart) And IsDate(Me.txtEnd) Then
        Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
    End If
End Sub

Private Sub txtEnd_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtEnd) Then
        MsgBox "Enter valid End date"
        Cancel = True
    End If
End Sub

Private Sub txtEnd_AfterUpdate()
    If IsDate(Me.txtStart) And IsDate(Me.txtEnd) Then
        Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
    End If
End Sub

This will 1) insure each text box has a valid date and/or time and 2) not call the Function until BOTH text boxes have a valid date and/or time.

mx

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
typo:

"when you end a value in txtStart" >>  when you ENTER a value in txtStart
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
just want to make sure you know to test everyone's solutions seperately, and not combine them (they are all different).

Also in the solution I posted at http:#a36890628

This:
 
   ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom

Open in new window


Should be changed to this:
 ' get duration as date time data type
    dtmTime = CDate(dtmTo) - CDate(dtmFrom)

Open in new window

Full code:

Option Compare Database
' ***** Change date parameters to Variants  *****
Public Function TimeDuration(dtmFrom As Variant, dtmTo As Variant, _
            Optional blnShowdays As Boolean = False) As String

'****** Add handling for null parameters
        if IsDate(dtmFrom) = False or isDate(dtmTo) = False Then
                    '*** one or both parameters is Null or some other non-date
                    ' do something, or just exit the function
                    Exit Function
        End if
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = CDate(dtmTo) - CDate(dtmFrom)
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
End Function  

Open in new window



The check for nulls here is in the function rather than in event code, so your existing code to call the function would remain the same.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I really see no need to even call the Function until you have confirmed - at the data entry point - that both text boxes contain valid date and/or times.  Otherwise, you are just bouncing in/out of the function trying to get the user to input valid date/time values.

mx
0
 

Author Comment

by:SerinaStar
Comment Utility
Hi Mx

Thank you very much for the helpful advise.
I have updated my code with what you suggested.

Private Sub txtStart_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtStart) Then
        MsgBox "Enter valid Start date"
        Cancel = True
    End If
End Sub

Private Sub txtStart_AfterUpdate()
    If IsDate(Me.txtStart) And IsDate(Me.txtEnd) Then
        Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
    End If
End Sub

Private Sub txtEnd_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtEnd) Then
        MsgBox "Enter valid End date"
        Cancel = True
    End If
End Sub

Private Sub txtEnd_AfterUpdate()
    If IsDate(Me.txtStart) And IsDate(Me.txtEnd) Then
        Me.txtShiftDuration = TimeDuration([txtStart], [txtEnd])
    End If
End Sub

However now it is not calculating a shift duration.......I don't know what I have done wrong.....please help!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Have you tried the other two suggestions here?
0
 

Author Comment

by:SerinaStar
Comment Utility
I was just trying them out I seem to still get an error.  I have just tested you code and it calculates however i recieve a run time error '13' Type mismatch which when I debug it appears to be Line 29 on the function.

' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then

Have I missed something?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Try revising the code like this:

Option Compare Database
' ***** Change date parameters to Variants  *****
Public Function TimeDuration(dtFrom As Variant, dtTo As Variant, _
            Optional blnShowdays As Boolean = False) As String

'****** Add handling for null parameters
        if IsDate(dtmFrom) = False or isDate(dtmTo) = False Then
                    '*** one or both parameters is Null or some other non-date
                    ' do something, or just exit the function
                    Exit Function
        End if
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmFrom as Date
    Dim dtmTo as Date
    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    

    dtmFrom = cDate(dtFrom)
    dtmTo = cDate(dtTo)
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
End Function   

Open in new window


Also, please reply to the suggestion posted by capricorn1.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Also,  I'm not sure why you have the if block on line 29 in your code at all.  It seems like you can change this:


    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If

Open in new window


To this:


    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
            dtmFrom = dtmFrom - 1
    End If

Open in new window


And it would do what you described in the comments.
0
 

Author Comment

by:SerinaStar
Comment Utility
Ok I have tried to reverse the code as you suggested above. When I enter times into the form I recieve the below error.
Complie error:
Duplicate declaration in current scope

It refers to line 22

Dim dtmFrom As Date

It is also not calculating a shift duration I guess due to the error.

This one is being a real learning curve for me you help is much appricated.

Oh and yes I will reply to Capricorn1 I am in the middle of trailing his suggestion.
0
 

Author Comment

by:SerinaStar
Comment Utility
Sorry I made a mistake for the error reference it is the frist statement:

Public Function TimeDuration(dtFrom As Variant, dtTo As Variant, _
            Optional blnShowdays As Boolean = False) As String
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
Comment Utility
Did you comment out the previous version of that function?  You should not have two versions of a function in your database at any given time.  Make backups of the database and either rename, comment out or overwrite previous versions.
0
 

Author Comment

by:SerinaStar
Comment Utility
Ah I just relised that I did not do that. It is working well now. Thanks very much for you help.

And I just tested Capricorn1's suggestion it work well too.

Thanks again it is much appricated!!
:)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Glad to help out.

Fwiw, I think mx's method might also work with that last revision of the function (the function itself was problematic).
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"Fwiw, I think mx's method might also work with that last revision of the function (the function itself was problematic)."

Actually, it does work.  And I tested what I posted with the *original* function posted by the OP on my system.

Also, try entering something that is not a date with the Accepted solution ...like abc.  You will be an immediate Type Mismatch error in the function. Or, if you enter 1234 (no colon), you will get a completely bogus answer.

mx
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now