Midnight Hour

[James Frater]

Hello Chaps,

I have a lot of events that finish at midnight, and like I can with MS Excel
I'd like to use the format 24:00 as it makes it easier to add up hours and
feed a FlexGrid.

However I can't get MS Access to recognise 24:00 and it keeps defaulting to
00:00 which then knocks off all my calculations and I have to use 23:59.

I've tried changing the input mask and format options in the table and form
designs but nothing seems to work.

Any suggestions, as always, are greatly appreciated.

JAMES

 

[Wayne-I-M]

Hi

On a 24hrd clock midnight is 00:00 - or if you set the format right you will
get 12:00:00 AM

you try this for a format
h:nn:ss ampm

and this as an input mask
00:00;0;_
This will give you 12:00:00 AM when you put in 00:00

Good luck

 

[Jeff Boyce]

James

It sounds like it might be an issue of display rather than what's stored.
It might be a bit of a kludge, but I suppose you could create a new field in
a query that uses an IIF() statement to display a 2400 if the time-portion
of your date/time field is .00000 ... and shows the time in hhmm format
otherwise.

Good luck

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Wayne-I-M]

Good point Jeff

I didn't think of that

SELECT
IIf([TableName]![TimeField]=0,Format([TableName]![TimeField],"00\:00"),[TableName]![TimeField]) AS NewTime
FROM TableName;

or (simpler)
SELECT Format([TableName]![TimeField],"00\:00") AS NewTime
FROM TableName;

 

[James Frater]

Wayne,

Thanks for replying.

I'd already gone down the 00:00;0;_ and h:nn:ss ampm avenue, it lists times
perfectly and makes sense to look at.

However, I run up against a problem when trying to use to add up times. For
example if I've got an event in from 23:20 to 00:00 using the calculation
below it will return a duration of 1400minutes instead of 40.

=(Hour([eventoff]-[eventon])*60)+(Minute([eventoff]-[eventon]))

Cheers

JAMES

 

[Dale Fye]

How are you storing your times?

When I first started using databases, I would occassionally store a time
value in a field, but then realized that when you had Start/End times that
spanned two days, it made things extremely difficult (as you have
experienced).

For that reason, I stopped storing time values and now only use complete
date/time information, so every date/time field contains date and time
information.

If you know your data is formatted properly, and your eventon and eventoff
times will never exceed 24 hours, you could first test to determine whether
eventoff is < eventOn. If so, add 1 to it, someting like:

=Datediff("n", eventon, iif(eventoff < eventon, 1+eventoff, eventoff)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Wayne,

Thanks for replying.

I'd already gone down the 00:00;0;_ and h:nn:ss ampm avenue, it lists times
perfectly and makes sense to look at.

However, I run up against a problem when trying to use to add up times. For
example if I've got an event in from 23:20 to 00:00 using the calculation
below it will return a duration of 1400minutes instead of 40.

=(Hour([eventoff]-[eventon])*60)+(Minute([eventoff]-[eventon]))

Cheers

JAMES

Hi

On a 24hrd clock midnight is 00:00 - or if you set the format right you will
get 12:00:00 AM

you try this for a format
h:nn:ss ampm

and this as an input mask
00:00;0;_
This will give you 12:00:00 AM when you put in 00:00

Good luck

 

[Wayne-I-M]

I'm still a little lost with this.

What is the reason you are not keeping things very simple.

Format([TimeA] -1 -[TimeB], "Short Time")

Sorry but maybe I have misunderstood your problem

--
Wayne
Manchester, England.

Wayne,

Thanks for replying.

I'd already gone down the 00:00;0;_ and h:nn:ss ampm avenue, it lists times
perfectly and makes sense to look at.

However, I run up against a problem when trying to use to add up times. For
example if I've got an event in from 23:20 to 00:00 using the calculation
below it will return a duration of 1400minutes instead of 40.

=(Hour([eventoff]-[eventon])*60)+(Minute([eventoff]-[eventon]))

Cheers

JAMES

Hi

On a 24hrd clock midnight is 00:00 - or if you set the format right you will
get 12:00:00 AM

you try this for a format
h:nn:ss ampm

and this as an input mask
00:00;0;_
This will give you 12:00:00 AM when you put in 00:00

Good luck

 

[James Frater]

Wayne,

Sorry for only getting back to you now. Format([TimeA] -1 -[TimeB], "Short
Time") works perfectly in all of my reports, forms and subforms, however all
of my Sum() expressions don't work any tips?

Cheers

JAMES

I'm still a little lost with this.

What is the reason you are not keeping things very simple.

Format([TimeA] -1 -[TimeB], "Short Time")

Sorry but maybe I have misunderstood your problem

--
Wayne
Manchester, England.

Wayne,

Thanks for replying.

I'd already gone down the 00:00;0;_ and h:nn:ss ampm avenue, it lists times
perfectly and makes sense to look at.

However, I run up against a problem when trying to use to add up times. For
example if I've got an event in from 23:20 to 00:00 using the calculation
below it will return a duration of 1400minutes instead of 40.

=(Hour([eventoff]-[eventon])*60)+(Minute([eventoff]-[eventon]))

Cheers

JAMES

Hi

On a 24hrd clock midnight is 00:00 - or if you set the format right you will
get 12:00:00 AM

you try this for a format
h:nn:ss ampm

and this as an input mask
00:00;0;_
This will give you 12:00:00 AM when you put in 00:00

Good luck
--
Wayne
Manchester, England.



:

Hello Chaps,

I have a lot of events that finish at midnight, and like I can with MS Excel
I'd like to use the format 24:00 as it makes it easier to add up hours and
feed a FlexGrid.

However I can't get MS Access to recognise 24:00 and it keeps defaulting to
00:00 which then knocks off all my calculations and I have to use 23:59.

I've tried changing the input mask and format options in the table and form
designs but nothing seems to work.

Any suggestions, as always, are greatly appreciated.

JAMES

 

[John W. Vinson]

Wayne,

Sorry for only getting back to you now. Format([TimeA] -1 -[TimeB], "Short
Time") works perfectly in all of my reports, forms and subforms, however all
of my Sum() expressions don't work any tips?

PMFJI... This Format expression returns a human-readable TEXT STRING. You
cannot sum "3:12" any more than you can sum "pizza".

Even if you leave off the Format() expression, a Date/Time value 3:12 can be
summed only with major restrictions. A Date/Time value is actually stored as a
number, a count of days and fractions of a day since midnight, December 30,
1899. It doesn't matter how it's generated - it's still a point in time on a
date, and it makes as little sense to sum as it would to add 8:00am to 11:45am
to 6:15pm and expect to get a meaningful result!

You would really be better off to store durations as just plain numbers, not
date/time datatypes. For instance, if the desired precision of durations is to
the nearest minute, store Long Integer minutes. These numbers can be added,
averaged, subtracted, etc.; they're just numbers, and 480 + 705 + 1095 is a
perfectly meaningful thing to sum. You can *DISPLAY* such a sum for human
consumption using an expression like

[duration] \ 60 & Format([duration] MOD 60, ":00")