Time calc.

[Dan @BCBS]

In the code below I'm trying to capture an amount of time between two dates.
Now()),Date()-[tr_date_timercvd_hoi]),

If the time period is 1 or more days it calcualtes fine....
What I need is, if it's only hours (<1day) I need to calculate the hours...
Example of what I need = .3 H (for 3 hours)..

CAN YOU PLEASE HELP
------------
FORM
AgeErisa =IIf([TR_Expedited]=True,"H"," D")

QUERY
AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))
-----------------------

 

[SteveS]

In the code below I'm trying to capture an amount of time between two dates.
Now()),Date()-[tr_date_timercvd_hoi]),

If the time period is 1 or more days it calcualtes fine....
What I need is, if it's only hours (<1day) I need to calculate the hours...
Example of what I need = .3 H (for 3 hours)..

CAN YOU PLEASE HELP
------------
FORM
AgeErisa =IIf([TR_Expedited]=True,"H"," D")

QUERY
AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

Dan,

Your IIF() function is really hard to follow, so I re-wrote it as a function.

Paste the following in a standard module: (watch for line wrap)

'---beg code -----
Public Function DaysHours(sinquirytype As String, dTIMERCVD, dclosedate,
bexpedited As Boolean)

If sinquirytype = "in" Then
DaysHours = Null
Else
If dTIMERCVD < #7/1/2002# Then
DaysHours = Null
Else
If IsNull(dclosedate) Then
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, Now()) '& " hours"
Else
DaysHours = Date - dTIMERCVD '& " days"
End If
Else
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, dclosedate) '& " hours!!"
Else
DaysHours = Date - dTIMERCVD '& " days!!"
End If
End If
End If
End If

If IsNull(DaysHours) Then
DaysHours = ""
End If
End Function
' --- end code --------



Usage:

In a column of a query: (should be one line)

AgeErisa: DaysHours([tr_inquirytype], [TR_DATE_TIMERCVD_HOI], [tr_closedate],
[tr_expedited])


I think this should be close to what you want.

HTH

 

[Dan @BCBS]

Were you able to execute this moduel??
I'm getting a compile error - expected: identifier

I've examened the code and don't see any missing ( or ).
Or any other thing missing... could you copy this in and try to run it
please...

Thanks for your time.
Dan
////////////////////////////

Option Compare Database

Public Function DaysHours(sinquirytype As String, dTIMERCVD, dclosedate,
bexpedited As Boolean)

If sinquirytype = "in" Then
DaysHours = Null
Else
If dTIMERCVD < #7/1/2002# Then
DaysHours = Null
Else
If IsNull(dclosedate) Then
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, Now()) '& " hours"
Else
DaysHours = Date - dTIMERCVD '& " days"
End If
Else
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, dclosedate) '& " hours!!"
Else
DaysHours = Date - dTIMERCVD '& " days!!"
End If
End If
End If
End If

If IsNull(DaysHours) Then
DaysHours = ""
End If
End Function
' --- end code --------


/////////////////////////

In the code below I'm trying to capture an amount of time between two dates.
Now()),Date()-[tr_date_timercvd_hoi]),

If the time period is 1 or more days it calcualtes fine....
What I need is, if it's only hours (<1day) I need to calculate the hours...
Example of what I need = .3 H (for 3 hours)..

CAN YOU PLEASE HELP
------------
FORM
AgeErisa =IIf([TR_Expedited]=True,"H"," D")

QUERY
AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

Dan,

Your IIF() function is really hard to follow, so I re-wrote it as a function.

Paste the following in a standard module: (watch for line wrap)

'---beg code -----
Public Function DaysHours(sinquirytype As String, dTIMERCVD, dclosedate,
bexpedited As Boolean)

If sinquirytype = "in" Then
DaysHours = Null
Else
If dTIMERCVD < #7/1/2002# Then
DaysHours = Null
Else
If IsNull(dclosedate) Then
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, Now()) '& " hours"
Else
DaysHours = Date - dTIMERCVD '& " days"
End If
Else
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, dclosedate) '& " hours!!"
Else
DaysHours = Date - dTIMERCVD '& " days!!"
End If
End If
End If
End If

If IsNull(DaysHours) Then
DaysHours = ""
End If
End Function
' --- end code --------



Usage:

In a column of a query: (should be one line)

AgeErisa: DaysHours([tr_inquirytype], [TR_DATE_TIMERCVD_HOI], [tr_closedate],
[tr_expedited])


I think this should be close to what you want.

HTH

 

[SteveS]

Were you able to execute this moduel??
I'm getting a compile error - expected: identifier

I've examened the code and don't see any missing ( or ).
Or any other thing missing... could you copy this in and try to run it
please...

Thanks for your time.
Dan
////////////////////////////

Dan,

Sorry it took so long to get back to you. Snowplow broke (took 3 days to
fix), then worked o the honeydo list


The function returns a string (actually a variant) in days or hours..
I'm not sure what you mean by .3H (for 3 hours)??

(.3 H = 20 minutes
3 hours = .125 D
..3 days = 7.2 hours)


The arguments (in order) are:

sinquirytype -> string ("in", "out")
dTIMERCVD -> date & time (1/10/2006 8:00AM, 1/10/2006 15:00)
dclosedate -> date & time (1/11/2006 1:00PM, 1/11/2006 17:00)
bexpedited -> boolean (true/false)

I added 4 controls to a form and tested the code. It returns whole days or
hours. If you want hours & minutes, you can modify the code to calc minutes
and convert.

I have this in a standard module, but you could put it in a class module
also. I shortened the code again.


'-----------begin code --------------------------
Public Function DaysHours(sinquirytype As String, dTIMERCVD, dclosedate,
bexpedited As Boolean)

If sinquirytype = "in" Then
DaysHours = Null
Else
If dTIMERCVD < #7/1/2002# Then
DaysHours = Null
Else
If bexpedited = True Then
DaysHours = DateDiff("h", dTIMERCVD, Nz(dclosedate, Now()))
& " hour"
Else
DaysHours = DateDiff("d", dTIMERCVD, Date) & " day"
End If
End If
End If
If Val(DaysHours) <> 1 Then
DaysHours = DaysHours & "s"
End If

End Function
'--------end code ------------

HTH