Using MS Access/Formatting a date
I have a question that I hope isn't overly simple. I have two sets of dates formatted in the following
Admit: 03/01/2013 15:45
FHR: 03/05/2013 15:45
Total: (the difference in time between FHR and admit#
The problem is that when I calculate this field there are a few problems.
1: negative entries #data collection/entry errors)
2: Some events may take months and I need to sort the values by Days:Hours:Minutes
When I do this [hh:nn];@ I get the right hh:mm but the hours don't seem to work once it hits 99 hours.
Admit: 7/17/2012 10:20:00 AM
FHR: 7/31/2012 8:46:00 PM
Output/Total: [10:26]- Should be 14(days):10(hours):00(Min)
I am still very new to this and do not know code yet. Is there a way to format this date in the design view of each table for a proper output or perhaps create another field with a conversion?
If so how would I go about doing that?
Thank you so much!
No, because it doesn't matter how a date is formatted. Formatting is just for display. Access stores dates as a double precision number where the integer portion represent the number of days since 12/30/1899 and the decimal portion is a fraction of a day. So that 6AM (or 6 hours) = .25.
So you have to take this into account when doing date arithmetic.
First, lets talk about the negative results. This is handled by doing a check after FHR is entered to see if it is earlier than Admit. You can use the After Update event of the FHR control on your form to run code like:
If Me.FHR < Me.Admit Then
MsgBox "FHR has to be later than Admit"
Me.FHR = Null
This will popup a message that FHR is earlier than Admit, clear what was entered and place focus in the FHR control so it can be re-entered.
No for the calculation. If you just subtract Admit from FHR and format as general Date the result will be in date notation and be 1/13/1900 10:26 AM. Which is not what you want. So the best way to handle this is with a custom function. Something like this:
Public Function TimeDifference(dteStart As Date, dteEnd As Date) As String
Dim intDays As Integer, intMin As Integer, intHrs As Integer, lngDiff As Long
lngDiff = DateDiff("n",dteStart, dteEnd)
intDays = lngDiff / 1440
intMin = lngDiff - (intDays * 1440)
intHrs = intMin /60
intMin = intMin MOD 60
TimeDifference = intDays & ":" & Format(intHours, "00") & ":" & Format(intMin,"00")
What this function does is first calculate the different in the two times in Minutes. It then divides that by 1440 (# of minutes in a day) to get the number of days). It then calculates the remaining minutes and does similar calcs to get the hours and minutes. It then outputs the difference as a string. In your example:
To use this, you would place the code in a Global Module and wherever you need to display the difference use:
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA