Ok here is the calculated field from my query and my primary function. (BTW,
when I don't use this calculated field I don't receive the #0 error).
CALC FIELD:
RgtHrsRunTime: IIf(IsNull([RunEndDte]),[RgtHrs],HoursMin(ThawDuration
([MSET_ID],[CycleNum],[ThawDte],[RunEndDte])))
CODE:
Public Function ThawDuration(ByVal lngMSET As Long, _
ByVal intCycle As Integer, ByVal dteThaw As Date, ByVal dteRunEnd As
Variant) As Long
On Error GoTo ErrorHandler
'This function calculates the total thaw duration of a matched set. It will
add previous cycle totals if they exist.
'The thaw duration will be used primarily for the Worklist report and will
be called from the qryReagent1 query.
'It will be based on Run End Date. If no Run date has been entered, the
query will use the RgtHrs calculated
'RgtHrs is based on the CycleTotal calculated field which uses the Select
statement below. qryCycleSum is a Totals query.
'CycTotal: IIf((SELECT qryCycleSum.SumofSecs
'FROM qryCycleSum WHERE qryRptReagent.MSET_ID = qryCycleSum.MSET_ID) Is
Null,0,(SELECT qryCycleSum.SumofSecs
'FROM qryCycleSum WHERE qryRptReagent.MSET_ID = qryCycleSum.MSET_ID)).
'qryReagent1 is based on another query (qryReagents) which is based on a
combo query of tables and qryReagentTime.
'qryReagentTime is based on a single table. Each query has at least 1
calculated field.
Dim lngTtlSecs As Long 'total cycle seconds
Dim lngPrevSecs As Long 'previous cycle seconds
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
If intCycle = 1 Then
lngTtlSecs = DateDiff("s", dteThaw, dteRunEnd)
End If
'Open a recordset of the qryRunTime query where MSET_ID is equal to the
value passed
Set rst = db.OpenRecordset("SELECT * FROM qryRunTime WHERE MSET_ID = " &
lngMSET)
If rst.RecordCount <> 0 Then
Select Case intCycle
Case 2
rst.Filter = "CycleNum = " & intCycle - 1
Set rst = rst.OpenRecordset()
lngPrevSecs = rst![CycleSecs]
lngTtlSecs = DateDiff("s", dteThaw, dteRunEnd) + lngPrevSecs
Case 3
rst.Filter = "CycleNum < " & intCycle
Set rst = rst.OpenRecordset()
rst.MoveFirst
lngPrevSecs = rst![CycleSecs]
rst.MoveLast
lngPrevSecs = lngPrevSecs + rst![CycleSecs]
lngTtlSecs = DateDiff("s", dteThaw, dteRunEnd) + lngPrevSecs
Case 4
rst.Filter = "CycleNum < " & intCycle
Set rst = rst.OpenRecordset()
rst.MoveFirst
lngPrevSecs = rst![CycleSecs]
rst.MoveNext
lngPrevSecs = lngPrevSecs + rst![CycleSecs]
rst.MoveLast
lngPrevSecs = lngPrevSecs + rst![CycleSecs]
lngTtlSecs = DateDiff("s", dteThaw, dteRunEnd) + lngPrevSecs
End Select
Else
MsgBox "No Data Available", vbOKOnly + vbInformation, "No Data"
Exit Function
End If
ThawDuration = lngTtlSecs
ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " & Err.DESCRIPTION &
".", vbOKOnly + vbExclamation, "Error"
GoTo ReleaseObjects
ReleaseObjects:
Set rst = Nothing
Set db = Nothing
End Function
Klatuu said:
are the [field1], [field2].... Date fields? Without seeing what the function
TotalTime does, I don't have an answer
LeAnn said:
Thanks see comments below. I have a connected question. I put some error
handling code in the procedure and now discover that I'm recieving "#0
Description:" error for each record on execution. I can't find any
documentation on this error. Can you help?
Comments:
I tested the field and it IS Null not an empty string. Your question "what
do you want to do if it is null", sparked my brain.
I thought I was
handling it in the code but for what ever reason, the IF statement wasn't
evaluation it properly. I need to get the funtion to return a total time for
a set (previous cycle times added to current run) at run. It all works fine
if there is a Run End time but didn't work if that field hadn't been entered.
So I wanted the calculation to use the RunStart time field.
I'd still like to know why the "null" value causes the error but I am using
a different function to do that calculation so now my query has this:
iif(IsNull[EndRun], [RgtHrs], TotalTime([field1], [field2], [field3],
[field4], [field5])
Klatuu said:
It may not be Null. It could be an empty string. To test correctly, check
for both.
The question is, What do you want to do with it if it is not a valid date?
=Iif(IsNull(MyDate) or MyDate = "",*Whatever Value you want for an empty
date*, Mydate)
:
Hello,
I have Access 97. I created a Function to determine cycle times for a given
"set". I am calling the function from a query to generate a report. I am
passing 5 variables (fields in the query). 3 of the fields are date/time
fields. One of the date fields may not have a value at times. When that is
the case, I receive an error is returned for the function. Using
debug.print I find that particular record does not print out when I run the
report for a particular set. There are 8 records but only 7 printed in the
debug window (though all eight pages appear in the report). The one without
a value in that date field did not print in the debug window.
How do I handle variables that have no value (especially date fields). In
my code I use an IF statement to evaluate whether that particulate date field
is null. That part is not evaluating properly when the field is null.
Thanks for any assistance!
