problem with date time field - if time=midnight loose time

  • Thread starter Thread starter Michael San Filippo
  • Start date Start date
M

Michael San Filippo

In a date field i have
11/4/2001 12:00:00 PM - this works fine however when I have 11/4/2001
12:00:00 AM it changes it to 11/4/2001 and looses the time field

I need this because I am making calculations on it - even if i change the
time to 11/4/2001 12:00:01 AM this keeps the time - but midnight takes it
away. any ideas?

thanks
Michael
 
Dates are actually Double's - where number to the left of the separator
represents dates and number to the right represent the time.
If you have a date and time like 11/4/2001 12:00:00 PM - it is equal to
37199 while 11/4/2001 12:00:00 PM - is equal to 37199,5.
Thats why your digits are stripped 37199.00 is the same as 37199.
Try to use doubles when calculating date and times - I find it much easier
to handle.
You can use the CDbl() function to convert a date/time to a double.
 
I need this because I am making calculations on it - even if i change the
time to 11/4/2001 12:00:01 AM this keeps the time - but midnight takes it
away. any ideas?
Click to expand...

It does not make any difference to the calculation. Access helpfully
recognises a DateTime with a TimeValue of zero as a plain date and displays
it as such. You can easily override the behaviour by setting the Format
property on the text box (or on the field too) to something like Long Time
or "dd/mm/yyyy hh:nn:ss", depending how explicit you need to be.

Hope that helps


Tim F
 
It doesn't really take it away.
MSA stores dates and times as numbers
The Integer part is the number of days since some past event (Say Bill
Gate's Birthdate)
The Fractional Part id the time since midnight in milliseconds or
something like that.

So when you set the dtm to 11/4/2001 12:00:00 your are making the
number = 37199.0 exactly. The general dateTime format decides that is
just a date, and doesn't display the time portion

Calculations based on it will still work though
If you want to see the Time even for midnight specify your own format
dd/mm/yyyy hh:nn:ss p should work. (Not sure of the AMPM indicator. I
use 24Hr time exclusively these days)

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
 
It doesn't really take it away.
MSA stores dates and times as numbers
The Integer part is the number of days since some past event (Say Bill
Gate's Birthdate)
Click to expand...

The Fractional Part id the time since midnight in milliseconds or
something like that.
Click to expand...

Fractions of a day - i.e. noon is 0.5, 6 pm is 0.75.
So when you set the dtm to 11/4/2001 12:00:00 your are making the
number = 37199.0 exactly. The general dateTime format decides that is
just a date, and doesn't display the time portion
Click to expand...

QUITE correct!
 
Hey guys - Thanks alot - but I figured out how to do it, thing was when i
was doing this it was coming up wierd

schedhours = Abs(DateDiff("n", Format(Schedaend, "short time"),
Format(schedast, "short time")))

So i did this instead

If Schedaend Like "##?##?####" Or Schedaend Like "#?#?####" Then
schedhours = Abs(DateDiff("n", Format([schedast], "general date"),
Format([Schedaend], "general date") & " 12:00:00am"))
Else
schedhours = Abs(DateDiff("n", Format(Schedaend, "short time"),
Format(schedast, "short time")))
If schedhours <> 0 Then schedhours = 1440 - schedhours
End If

Thanks again!
 
Back
Top