Full Version: Invalid use of Null - error '94'
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
bakersburg9
Jun 15 2007, 09:13 AM
I have a report based on a query that uses a function I got here that is called in the query - it calculates elapsed time - if there are any missing times, I get an error - is there any error handling I could add to where I would get a message that a time is missing for the calculation ? If it could open the form to the record with the missing time, that would be awesome

CODE
Function GetElapsedTimeNew(interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long
Dim strET As String

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60

If days > 0 Then
  strET = days & " Day"
  If days > 1 Then
    strET = strET & "s"
  End If
strET = strET & " "
End If

If hours > 0 Then
  strET = strET & hours & " Hour"
  If hours > 1 Then
    strET = strET & "s"
  End If
strET = strET & " "
End If

If Minutes > 0 Then
  strET = strET & Minutes & " Min"  
  If Minutes > 1 Then
    strET = strET & "s"
  End If
strET = strET & " "
End If

'If Seconds > 0 Then
'  strET = strET & Seconds & " Second"
'  If Seconds > 1 Then
'    strET = strET & "s"
'  End If
'strET = strET & " "
'End If
'
GetElapsedTimeNew = strET

End Function
niesz
Jun 15 2007, 09:22 AM
It seems to me that you should handle this in the query.

SELECTGetElapsedTimeNew(YourInterval) FROM YourTable WHERE YourInterval IS NOT NULL

..and if you need to find the null intervals..

SELECT YourInterval FROM YourTable WHERE YourInterval IS NULL
Candace Tripp
Jun 15 2007, 09:26 AM
I assume it's "interval" that is missing and that it is a long:

Change your procedure parameters as follows to set a default value that won't cause an error:

CODE

Function GetElapsedTimeNew(Optional interval As Long = 0) As String
bakersburg9
Jun 15 2007, 09:43 AM
Walter,
with SELECT YourInterval FROM YourTable WHERE YourInterval IS NULL
I get data type mismatch

thank you and candace for your input - the function causes the error to go away, but it doesn't include the missing record - I need to be warned there is a record without a time value

thanks again,
Steve
niesz
Jun 15 2007, 09:47 AM
>>with SELECT YourInterval FROM YourTable WHERE YourInterval IS NULL
I get data type mismatch<<

Post the SQL you're using. There's nothing in what I provided to be mismatched.
bakersburg9
Jun 15 2007, 09:58 AM
Walter,

You're right - i had incorp. CT's code and somehow it was causing all intervals to be null - but since it didn't err, I didn't catch it. But I still need to figure out how to handle errors.

Steve
bakersburg9
Jun 15 2007, 10:04 AM
Here's the SQL:

SELECT qryDevelopmentNotesWithTime.ElapsedTime
FROM qryDevelopmentNotesWithTime
WHERE qryDevelopmentNotesWithTime.ElapsedTime Is Null;

it still just says "Invalid use of NULL" if there is a time missing
R. Hicks
Jun 15 2007, 10:10 AM
You should query the table directly instead of querying a query ....
The problem may be in qryDevelopmentNotesWithTime ...

RDH
bakersburg9
Jun 15 2007, 10:24 AM
Ricky,
OK, that worked great - I re-wrote to :

CODE
SELECT tblDevelopmentNotes.ID, _
tblDevelopmentNotes.Description, tblDevelopmentNotes.StartTime2, tblDevelopmentNotes.EndTime2
FROM tblDevelopmentNotes
WHERE (((tblDevelopmentNotes.EndTime1) Is Null)) OR (((tblDevelopmentNotes.StartTime2) Is Not Null) _
AND ((tblDevelopmentNotes.EndTime2) Is Null));


works great - this is much better - I can make a sub-form with the query as source

Thanks !!!!!!!

Steve
datAdrenaline
Jun 15 2007, 10:31 AM
Hello Steve ...

Since you know what is causing the problem, why not just coerce the field with the Nz() function ...

Expression: GetElapsedTimeNew(Nz([FieldWithInterval], 0))

Also, to help out with other possible problems, I would suggest you declare the "interval" parameter as a particular datatype, that way something crazy like passing a form object to it will error out immediately. Without a specific datatype declaration, the varible is assigned the variant datatype which can accept just about anything!!
R. Hicks
Jun 15 2007, 10:34 AM
Glad you got it going ...

RDH
bakersburg9
Jun 15 2007, 10:34 AM
I'm sorry - you'll have to 'dumb it down' I didn't understand that - what problem does your expression solve, I'm sorry...
niesz
Jun 15 2007, 11:04 AM
Steve,

I think the root of this problem is in understanding what you want to happen when Nulls are encountered.

Do you want a message to say: "You can't run this report until you fix the Nulls"?
Do you want the query to return a line in with the other data that says, "This one is Null"?
Do you want to substitute 0's when Nulls are encountered?
Do you want to ignore the Nulls and run the other data?
Do you want some combination of the above?
...or something else?

All of the above solutions are pointed at solving one of these, but they may not be what you need. Please explain in depth the actions you need to take when Nulls are found.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.