Full Version: Show Fractions of Seconds in Format Function
UtterAccess Forums > Microsoft® Access > Access Date + Time
I have dates and times that are stored as numbers in my database. For example: 37874.3935901273 = 9/10/2003 9:26:46 AM
Is there a way to format this into a string that shows fractions of seconds? Right now I use the function below that does not show second fractions.
Ideally I would have string like this: mmddyyyyhhnnssff
Thanks in advance.
See this thread.

Odon't believe a datetime field in Access stores time to the millisecond level.
Here you go!
Function dtFrac(DateNumber As Double) As String
nbsp; Dim sDt As String
  Dim dtv As Double
  Dim dtf As Double
  'Time is stored in fractional part of a day(fpd)
  '?FormatNumber(1# / 24#, 20)      '1 hour in (fpd)
  '?FormatNumber(1# / 1440#, 20)    '1 minute in (fpd)
  '?FormatNumber(1# / 86400#, 20)   '1 second in (fpd)
  '37874.3935901273 = 9/10/2003 9:26:46 AM
  'get numerical value for the Date Time part that Access can handle
  sDt = Format(DateNumber, "mm/dd/yyyy hh:nn:ss")
  dtv = CDbl(CDate(sDt))
  'subtract DateTimeValue to get fractional seconds in (fpd)
  'dtf = DateNumber - dtv
  'convert from (fpd) to seconds
  'dtf = dtf * 86400#
  'convert seconds to fractional seconds (.18 to 18)
  'dtf = Int(dtf * 100#)
  'here I combined all of the conversion math into one step
  dtf = Int((DateNumber - dtv) * 8640000#)
  'add fractional seconds to required format
  sDt = Format(DateNumber, "mmddyyyyhhnnss") & Format(dtf, "00")
  dtFrac = sDt
End Function
THANKS!! It was the simple concept "dtf = DateNumber - dtv" that I was missing.
Thanks again.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.