After Midnite

[DS]

When is it really after midnite and not before?

If Me.TxtTime is => "12:00:00 AM" Then
MsgBox "After Midnite"
ElseIf Me.TxtTime < "12:00:00 AM Then
MsgBox "Before Midnite"
End If

When is the above expression not true? I know that at some point the above
expression won't be true...any help appreciated.
Thanks
DS

 

[Allen Browne]

Date/Time values in Access are stored as floating point numbers. The integer
part represents the number of days since day zero (Dec 30, 1899.) The
fractional part is the time as a fraction of a day (noon = 0.5, 6am = 0.25,
etc.)

Therefore any date prior to Dec 30 1899 is less than 12:00:00 AM. Note that
you must use # as the delimiter around date/time values, so you get a date
comparision, not a character-by-character text comparison.

To experiment, open the Immediate Window (Ctrl+G), and enter some
expressions like the ones below:

This tells you the zero date:
? Format(#00:00:00#, "Short Date")

Therefore this is true:
? #1/1/1800# < #12:00:00 AM#
but this is false:
? #1/1/1900# < #12:00:00 AM#

Use TimeValue() to parse the time component from a date/time.
Use DateDiff("n", ...) to get the number of minutes since midnight.

 

[DS]

Thanks Allen , I see this something that I'll have to play with! The
problem is that it doesn't go by date I have to go by Day 1 to 7 and the
time...
I have a form that contains data and the problem I'm having is if I have a
dinner menu that starts on Monday at 5:00 PM and ends at 1:00 AM Tuesday
morning it doesnt come up because Monday is day 2 and Tuesday is day 3.

So I was thinking of perhaps this...
Start Day Monday(2) Start Time 5:00PM
EndDay Tuesday(3) End Time 1:00AM

So how would I query this?

StartDay =< Now() and StartTime > Time()
EndDay = Now() and EndTime < Time()

Thanks Allen
DS

 

[Allen Browne]

Is there any chance of changing the struture so you have these fields
instead:
StartDateTime Date/Time both the date and time
Minutes Number duration in minutes

If you store the data in those 2 fields, you can use a query to derive
anything else you want, by typing an expression into the Field row in query
design. For example, to get the day of the week:
DOW: Weekday([StartDateTime])

To get the ending date and time:
EndDateTime: DateAdd("n", [Minutes], [StartDateTime])

To get the start time only:
StartTime: TimeValue([StartDateTime])

Do you see that you can then use expressions such as those to calculate
anything else you want to do.

If that is not practical, you will have to derive those expressions from the
fields you have. This kind of thing (untested example only):

Minutes: IIf(([EndDay] = [StartDay]) And ([EndTime] > [StartTime]),
DateDiff("n", [StartTime], [EndTime]),
IIf(([EndDay] = [StartDay] + 1) And ([EndTime] < [StartTime]),
DateDiff("n", [StartTime], [EndTime] + 1),
0))

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

And if you are thinking about the interface, you can design that so the user
enters the data any way you want, and behind the scenes your code combines
the start date and time, and calculates the number of minutes to store.
Ultimately, the way the user enters the data and the way you need to store
it are quite different things.

 

[DS]

Great Thanks! I think have it now!
DS

Is there any chance of changing the struture so you have these fields
instead:
StartDateTime Date/Time both the date and time
Minutes Number duration in minutes

If you store the data in those 2 fields, you can use a query to derive
anything else you want, by typing an expression into the Field row in
query design. For example, to get the day of the week:
DOW: Weekday([StartDateTime])

To get the ending date and time:
EndDateTime: DateAdd("n", [Minutes], [StartDateTime])

To get the start time only:
StartTime: TimeValue([StartDateTime])

Do you see that you can then use expressions such as those to calculate
anything else you want to do.

If that is not practical, you will have to derive those expressions from
the fields you have. This kind of thing (untested example only):

Minutes: IIf(([EndDay] = [StartDay]) And ([EndTime] > [StartTime]),
DateDiff("n", [StartTime], [EndTime]),
IIf(([EndDay] = [StartDay] + 1) And ([EndTime] < [StartTime]),
DateDiff("n", [StartTime], [EndTime] + 1),
0))

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

And if you are thinking about the interface, you can design that so the
user enters the data any way you want, and behind the scenes your code
combines the start date and time, and calculates the number of minutes to
store. Ultimately, the way the user enters the data and the way you need
to store it are quite different things.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen , I see this something that I'll have to play with! The
problem is that it doesn't go by date I have to go by Day 1 to 7 and the
time...
I have a form that contains data and the problem I'm having is if I have
a dinner menu that starts on Monday at 5:00 PM and ends at 1:00 AM
Tuesday morning it doesnt come up because Monday is day 2 and Tuesday is
day 3.

So I was thinking of perhaps this...
Start Day Monday(2) Start Time 5:00PM
EndDay Tuesday(3) End Time 1:00AM

So how would I query this?

StartDay =< Now() and StartTime > Time()
EndDay = Now() and EndTime < Time()

Thanks Allen
DS