Negativce Time Value Needed

[DKORO]

I have a patient waiting list
and we have Appointment time and Time()

essentially AppointmentTime-Time() = Waiting
If it is negative we need to see the patient immediately
if it is positive we are running on time everything is ok

DateDiff("n",[TimeIn],[TimeOut])
GIVES NUMBER OF MINUTES + or -

Yet I need Hrs and mins negative or positive difference
However

Format(DateDiff("n", TimeIn, TimeOUt) / 1440, "h:mm&quot
GIVES H:mm only +

What should I use

Thanks in Advance
Daniel

 

[davemcdonaldireland]

DKORO,

Here is a function that takes a number of minutes (- or +) and returns a formatted string like "H:MM" or "-H:MM":

[tt]
Public Function HoursAndMinutes(lngGivenMinutes As Long) As String
Dim lngMinutes As Long
Dim lngHours As Long

lngHours = Fix(Abs(lngGivenMinutes) / 60)
lngMinutes = Abs(lngGivenMinutes) - lngHours * 60

If lngGivenMinutes < 0 Then
HoursAndMinutes = &quot;-&quot; & CStr(lngHours) & &quot;:&quot; & Format(CStr(lngMinutes), &quot;00&quot
Else
HoursAndMinutes = CStr(lngHours) & &quot;:&quot; & Format(CStr(lngMinutes), &quot;00&quot
End If

MsgBox HoursAndMinutes

End Function
[/tt]

Does this help?


Dave Mc Donald

http://www.mcdonaldit.com

 

[danvlas]

The expression below:

(TimeValue(AppointmentTime)-time)*24*60

will give you a positive/negative value in minutes.
To obtain the result in seconds, multiply it by 60

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[DKORO]

It is part of an SQL statement so it must be some what short.

Below
Format(DateDiff(&quot;n&quot;, TimeIn, TimeOUt) / 1440, &quot;h:mm&quot
Works

However it does not allow me to have a negative value


How do I make it let me have a negaticve value. ie

-1:30 ' wow this person needs to be seen Stat
0:30 ' patient will be arriving for appointment
in 30 mins

Daniel thanks but your suggestion unfortunately is not quite it yet.
But Dave ... hmmm how could I put yours into an SQL hmm ie shorter??

Any advice apreciated

Daniel

 

[davemcdonaldireland]

Oh dear, putting my solution in SQL sounds impossible! Not to say it can't be done, of course.

What about just retrieving the TimeIn and TimeOut fields from the recordset and then using my function? i.e:

strQuery = &quot;SELECT TimeIn, TimeOut FROM tblWhatever WHERE PatientId = &quot; & lngPatientId
objRS.Open strQuery, CurrentDatabase.Connection

strTime = HoursAndMinutes(DateDiff(&quot;n&quot;, objRS(&quot;TimeIn&quot, objRS(&quot;TimeOut&quot))

objRS.Close



Dave Mc Donald

http://www.mcdonaldit.com

 

[DKORO]

(Sgn(DateDiff(&quot;n&quot;,[TimeIn],[TimeOut])))*(Format(DateDiff(&quot;n&quot;, TimeIn, TimeOUt) / 1440, &quot;h:mm&quot)


Does not work but would be good if it did it would solve my problem hmmmmmm????