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

[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

 

[Tim Ferguson]

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?

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

 

[Greg Kraushaar]

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)

 

[John Vinson]

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.

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

QUITE correct!

 

[Michael San Filippo]

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!