the code is as follows:
Public Function TATM(Startdate As Date, Enddate As Date)
Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Integer
NumDays = 0
Dim MinAdjust As Integer
Dim DayAdjust As Integer
DayAdjust = 1
Dim TotalMins As Long
Dim starthour As Date
starthour = Format(Startdate, "Long Time")
Dim endhour As Date
endhour = Format(Enddate, "Long Time")
Startdate = Format(Startdate, "Short Date")
Enddate = Format(Enddate, "Short Date")
Dim daystarthour As Date
Dim dayendhour As Date
daystarthour = "7:00"
dayendhour = "17:00"
' If the entry was logged before the day begins, move the
entry time up to the start of the day
If starthour < daystarthour Then
starthour = daystarthour
End If
' If the entry was logged after COB, move the entry time
to the beginning of the next day
If starthour > dayendhour Then
starthour = daystarthour
Startdate = DateAdd("d", 1, Startdate)
End If
'If the entry was closed after COB, move the close time to
the end of the day
If endhour > dayendhour Then
endhour = dayendhour
End If
'If the entry was closed before the day begins, move the
entry time up to the start of the day
If endhour < daystarthour Then
endhour = daystarthour
End If
'If a full day has not lapsed between the beginning and
end, adjust for the difference in times
If starthour > endhour Then
MinAdjust = DateDiff("n", starthour, dayendhour) +
DateDiff("n", daystarthour, endhour)
DayAdjust = 2
Else
MinAdjust = DateDiff("n", starthour, endhour)
End If
'Loop through the date range, adding only weekdays to the
NumDays counter
MyDate = Startdate
For Idx = CLng(Startdate) To CLng(Enddate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
' Do Nothing
Case Is = 7 'Saturday
' DO Nothing
Case Else 'Normal Workday
NumDays = NumDays + 1
End Select
MyDate = DateAdd("d", 1, MyDate)
Next Idx
'A day is defined as being 10 hours (600 minutes) long
TotalMins = ((NumDays - DayAdjust) * 600) + MinAdjust
'If Startdate = Enddate Then
' TotalMins = DateDiff("s", starthour, endhour)
'End If
TATM = TotalMins / 60
End Function
i'm using this same module for all of the queries, and
some of the i can format, and some i can't.
-----Original Message-----
I'm running a query that's calculating the turnaround time
in hours between two fields. Currently this number is
displaying with quite a few decimal places, but I only
want it to display two. In some of the queries, when I go
to Properties, I have the option to format the field as
fixed, and set the number of decimal places to 2, but in
some of the queries I'm just getting a blank drop-down box
in the Format section. Why is is doing this, and what can
I do to fix it?
Care to post the SQL of your query? It really does help...
What you might try is to calculate the hours with an expression like
Round(DateDiff("n", [start], [end])/60., 2)
.
