How to calculate datediff for a field based on two other fields?

[sueb]

I inherited a database that contains three fields:

- OR Time (a number)
- OR Time In Room (Date/Time)
- OR Time Out Room (Date/Time)

On the form is an unbounded field that correctly calculates and displays the minutes between the patient's entering the OR room and leaving the room (using DateDiff with the minutes option, 'n'). The "OR Time" field in every record in the database is blank.

I want to calculate that time difference based on any change to either of the Date/Time fields and store it in the field "OR Time". The department now wants a report that sums "OR Time"s on a monthly basis, and I don't want to waste time re-calculating this field everytime the report is run. I have set the field "OR Time" to Locked in preparation for making this work.

I haven't been able to figure out the syntax, however, to make this happen. I created the following public function to call "After Update" for either of the "room" fields:

Public Function Calculate_Minutes(Date1 As Date, Date2 As Date) As Number
    Calculate_Minutes = DateDiff("n", Date1, Date2)
End Function
 

but the error I get is "Compile error. User-defined type not defined." pointing to the function's declaration statement.

The call looks like this:

Private Sub OR_Time_Out_Room_AfterUpdate()
    Me.[OR Time] = Calculate_Minutes(Me.OR_Time_In_Room, Me.OR_Time_Out_Room)
End Sub
 

and generates no error of its own.

What am I doing wrong?

[ADezii]

The problem is with the Return Type of the Function, namely Number. There is no Data Type named Number. To play it safe, redefine your Data Type:

Public Function Calculate_Minutes(Date1 As Date, Date2 As Date) As Long

[sueb]

Okay, that makes sense. I thought "Number" was odd for a datatype, but I didn't get an error within the code window itself, so I thought it was okay.

So I fixed up all that (and the calls were a little wonky, too, it turns out), but I'm getting this from the public function:

"Reserved error (-1517); there is no message from this error"

which leaves me a little stumped as to where to go from here. Here's the function as it stands:

Public Function Calculate_Minutes(Date1 As Date, Date2 As Date) As Long
On Error GoTo Err_Calculate_Minutes
 
    Calculate_Minutes = DateDiff("n", Date1, Date2)
 
Exit_Calculate_Minutes:
    Exit Function
 
Err_Calculate_Minutes:
    MsgBox Err.Description
    Resume Exit_Calculate_Minutes
 
End Function
 

and the call from one of the fields:

Private Sub OR_Time_In_Room_AfterUpdate()
On Error GoTo Err_OR_Time_In_Room_AfterUpdate
 
    Me.[OR Time] = Calculate_Minutes(Me.OR_Time_In_Room, Me.OR_Time_Out_Room)
 
Exit_OR_Time_In_Room_AfterUpdate:
    Exit Sub
 
Err_OR_Time_In_Room_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_OR_Time_In_Room_AfterUpdate
 
End Sub

[ADezii]

It appears that it should work now, sueb, and I'm not really sure why it doesn't. If you wish to Attach the Front End, I'll be happy to look at it. I still have an Archived copy of the Back End, and I'm assuming that it is still applicable. If you do Attach the FE, please indicate what Form is the one in question.

[sueb]

I came in this morning and tried it again, right before getting ready to upload a copy, and it does work now.

It's not the first time that I've experienced that: I work on a bit of code for several hours into the evening; it looks like it should work but doesn't; I go home, come back, and just fire it off again in the morning, and it works.

Mystifying.

So, thanks again for steering me onto the path of righteousness, ADezii!

[sueb]

Here's another thread that addresses this -1517 error, and some of what it says sounds like it might be applicable to my situation. I'm going to investigate further.

http://bytes.com/topic/access/answer...-message-error

[NeoPa]

Sue, what you are suggesting (and now have working it appears) is exactly the sort of thing that we try to warn developers away from. This is de-normalising your data and is generally considered a very bad idea. The concepts behind normalisation, and why it is so important, are covered in Database Normalisation and Table structures. I highly recommend reading it, and reverting your database back to a more normalised form.

[sueb]

NeoPa, I realize that this is a little unorthodox, and at some point I'll probably re-work it. For now, I'm just going to leave it as is. I'm not even sure that the user's requirements around this issue are settled yet.

But thanks for the link--it's always good to have references handy!

[NeoPa]

Not my recomendation clearly, but as long as you're making an informed choice on the matter I can relax knowing I've done what I need to. I also understand, of course, the restrictions imposed when working on inherited databases. Always more tricky than ones you've designed yourself.

Good luck with your project :-)