How to combine date and time in the database

[Luvin lunch]

Hi,

I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.

I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.

So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?

I'm not sure if what I've suggested is completely ridiculous or not.
Any help would be gratefully received.

Thanks

MM

[Keith Wilby]

"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hi,

I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.

I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.

So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?

I'm not sure if what I've suggested is completely ridiculous or not.

Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.

You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:

Me.txtMyTextBox = Time

remembering, of course, that it will be updated every time the current
record is updated.

HTH - Keith.
www.keithwilby.com

[Luvin lunch]

Hi Keith,

Thanks for the suggestions below.

What I need to do is allow the user to enter a time and a date
themselves. I'm setting up a kind of reminder system so that the user
is reminded at a certain date and time in the future to ring certain
people. The user therefore will be entering a date and time. I can't
use the system time.

Once the date and time is saved, I will display a list of the calls the
user has to make ordered by the call date and time. Because I'm
ordering by date and time I want to keep the date and time in one field
so I'm wondering how can I combine the date inputted and the time
inputted so that it's stored in only one field in access. Is this even
possible?

Thanks again

MM
Keith Wilby wrote:

"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hi,

I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.

I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.

So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?

I'm not sure if what I've suggested is completely ridiculous or not.

Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.

You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:

Me.txtMyTextBox = Time

remembering, of course, that it will be updated every time the current
record is updated.

HTH - Keith.
www.keithwilby.com

[CDMAPoster]

Luvin lunch wrote:

Hi Keith,

Thanks for the suggestions below.

What I need to do is allow the user to enter a time and a date
themselves. I'm setting up a kind of reminder system so that the user
is reminded at a certain date and time in the future to ring certain
people. The user therefore will be entering a date and time. I can't
use the system time.

Once the date and time is saved, I will display a list of the calls the
user has to make ordered by the call date and time. Because I'm
ordering by date and time I want to keep the date and time in one field
so I'm wondering how can I combine the date inputted and the time
inputted so that it's stored in only one field in access. Is this even
possible?

Thanks again

MM
Keith Wilby wrote:

"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hi,
>
I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.
>
I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.
>
So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?
>
I'm not sure if what I've suggested is completely ridiculous or not.

Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.

You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:

Me.txtMyTextBox = Time

remembering, of course, that it will be updated every time the current
record is updated.

HTH - Keith.
www.keithwilby.com

It is my opinion that Microsoft should have created a built-in function
for adding two date values or parts of date values. Although the
probability that the underlying data type for Date/Time will ever
change is absurdly low, simply adding date values together relies on
the way dates are implemented. It is more robust to use date functions
to perform this. That way, if the way dates are stored internally is
changed, the built-in date function can be changed by Microsoft and
nothing will break. The downside of this is that nearly any function
you roll on your own will be more complex than performing the addition.
A reasonable compromise might be to have a tiny wrapper function that
uses addition internally with the realization that the wrapper function
must change if the date storage mechanism changes. Another option is
to perform an unwieldy addition using the DateAdd function. Doing it
this way will not require changes later.

E.g.,

Wrapper function:

Public Function AddDates(dt1 As Date, dt2 As Date) As Date
AddDates = dt1 + dt2
End Function

DateAdd method:

Public Function AddDates(dt1 As Date, dt2 As Date) As Date
Dim dtDayPart As Date
Dim intSeconds As Integer

dtDayPart = DateAdd("s", -DatePart("s", dt1), dt1) + DateAdd("s",
-DatePart("s", dt2), dt2)
intSeconds = DatePart("s", dt1) + DatePart("s", dt2)
AddDates = DateAdd("s", intSeconds, dtDayPart)
End Function

Note that I avoided using Int(dt1) and Int(dt2). Still, none of these
implementations, especially direct addition without a wrapper, leave me
satisfied. IMO, Microsoft should have maintained this interface. I
think it was a question of sacrificing good practice for ease of use
in, say, query criteria. You asked a very good question.

James A. Fortune
CD********@FortuneJames.com

[Per]

You should be able to define the data source of the hidden date/time
field as the visible date field + the visible time field. Working with
dates and times is actually just like working with numbers; the date
part of a date/time field is just an integer (number of days since a
specific start date), while the time portion is the fraction, ie
fraction of a 24-hour day.

Here's an example:
Control Source: =[DocumentDate]+[DocumentTime]

If you want to make sure you don't get a time part from the date field
or a date part from the time field, you would have to use DateSerial
and TimeSerial:

Control Source:
=DateSerial(Year([DocumentDate]),Month([DocumentDate]),Day([DocumentDate])+TimeSerial(Hour([DocumentTime]),Minute([DocumentTime]),Second([DocumentTime]))

Also, if you're not sure that the date or time fields have non-null
values in them, you should use the Nz() function:

Control Source:
=DateSerial(Year(nz([DocumentDate],0)),Month(nz([DocumentDate]),0),Day(nz([DocumentDate],0))+TimeSerial(nz(Hour([DocumentTime],0)),Minute(nz([DocumentTime],0)),Second(nz([DocumentTime],0)))

Hope that works.

/Per

Luvin lunch wrote:

Hi Keith,

Thanks for the suggestions below.

What I need to do is allow the user to enter a time and a date
themselves. I'm setting up a kind of reminder system so that the user
is reminded at a certain date and time in the future to ring certain
people. The user therefore will be entering a date and time. I can't
use the system time.

Once the date and time is saved, I will display a list of the calls the
user has to make ordered by the call date and time. Because I'm
ordering by date and time I want to keep the date and time in one field
so I'm wondering how can I combine the date inputted and the time
inputted so that it's stored in only one field in access. Is this even
possible?

Thanks again

MM
Keith Wilby wrote:

"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hi,
>
I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.
>
I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.
>
So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?
>
I'm not sure if what I've suggested is completely ridiculous or not.

Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.

You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:

Me.txtMyTextBox = Time

remembering, of course, that it will be updated every time the current
record is updated.

HTH - Keith.
www.keithwilby.com

[Luvin lunch]

Per wrote:

You should be able to define the data source of the hidden date/time
field as the visible date field + the visible time field. Working with
dates and times is actually just like working with numbers; the date
part of a date/time field is just an integer (number of days since a
specific start date), while the time portion is the fraction, ie
fraction of a 24-hour day.

Here's an example:
Control Source: =[DocumentDate]+[DocumentTime]

If you want to make sure you don't get a time part from the date field
or a date part from the time field, you would have to use DateSerial
and TimeSerial:

Control Source:
=DateSerial(Year([DocumentDate]),Month([DocumentDate]),Day([DocumentDate])+TimeSerial(Hour([DocumentTime]),Minute([DocumentTime]),Second([DocumentTime]))

Also, if you're not sure that the date or time fields have non-null
values in them, you should use the Nz() function:

Control Source:
=DateSerial(Year(nz([DocumentDate],0)),Month(nz([DocumentDate]),0),Day(nz([DocumentDate],0))+TimeSerial(nz(Hour([DocumentTime],0)),Minute(nz([DocumentTime],0)),Second(nz([DocumentTime],0)))

Hope that works.

/Per

Luvin lunch wrote:

Hi Keith,

Thanks for the suggestions below.

What I need to do is allow the user to enter a time and a date
themselves. I'm setting up a kind of reminder system so that the user
is reminded at a certain date and time in the future to ring certain
people. The user therefore will be entering a date and time. I can't
use the system time.

Once the date and time is saved, I will display a list of the calls the
user has to make ordered by the call date and time. Because I'm
ordering by date and time I want to keep the date and time in one field
so I'm wondering how can I combine the date inputted and the time
inputted so that it's stored in only one field in access. Is this even
possible?

Thanks again

MM
Keith Wilby wrote:

"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Hi,

I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.

I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.

So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?

I'm not sure if what I've suggested is completely ridiculous or not.
>
Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.
>
You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:
>
Me.txtMyTextBox = Time
>
remembering, of course, that it will be updated every time the current
record is updated.
>
HTH - Keith.
www.keithwilby.com

[Luvin lunch]

What I tried to say yesterday but it didn't work out was thanks for the
information. It's exactly what I was looking for.

MM

Luvin lunch wrote:

Per wrote:

You should be able to define the data source of the hidden date/time
field as the visible date field + the visible time field. Working with
dates and times is actually just like working with numbers; the date
part of a date/time field is just an integer (number of days since a
specific start date), while the time portion is the fraction, ie
fraction of a 24-hour day.

Here's an example:
Control Source: =[DocumentDate]+[DocumentTime]

If you want to make sure you don't get a time part from the date field
or a date part from the time field, you would have to use DateSerial
and TimeSerial:

Control Source:
=DateSerial(Year([DocumentDate]),Month([DocumentDate]),Day([DocumentDate])+TimeSerial(Hour([DocumentTime]),Minute([DocumentTime]),Second([DocumentTime]))

Also, if you're not sure that the date or time fields have non-null
values in them, you should use the Nz() function:

Control Source:
=DateSerial(Year(nz([DocumentDate],0)),Month(nz([DocumentDate]),0),Day(nz([DocumentDate],0))+TimeSerial(nz(Hour([DocumentTime],0)),Minute(nz([DocumentTime],0)),Second(nz([DocumentTime],0)))

Hope that works.

/Per

Luvin lunch wrote:

Hi Keith,
>
Thanks for the suggestions below.
>
What I need to do is allow the user to enter a time and a date
themselves. I'm setting up a kind of reminder system so that the user
is reminded at a certain date and time in the future to ring certain
people. The user therefore will be entering a date and time. I can't
use the system time.
>
Once the date and time is saved, I will display a list of the calls the
user has to make ordered by the call date and time. Because I'm
ordering by date and time I want to keep the date and time in one field
so I'm wondering how can I combine the date inputted and the time
inputted so that it's stored in only one field in access. Is this even
possible?
>
Thanks again
>
MM
>
>
Keith Wilby wrote:
"Luvin lunch" <ma************@o2.iewrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Hi,
>
I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.
>
I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date they enter
and would be linked to a calendar control. The second text box would be
of type short time and would allow the user to enter time. I also
thought I would have a hidden date/time field. It would be populated
by code I would run in the onChange events of the first two text boxes
and it would be bound to the date/time database field.
>
So to my questions:
Is this the simplest way of implementing date/time entry?
What function should I use to combine the date and time in the hidden
field?
>
I'm not sure if what I've suggested is completely ridiculous or not.

Not at all ridiculous. I've never used a calendar control but I would
imagine that, if the bound control's format is "General Date" then it would
take the time element from the system clock, but even if it doesn't you can
use the "Time()" function to return the system time.

You can also use the "Time()" function to populate your (hidden) text box
with the system time in the form's Before Update event:

Me.txtMyTextBox = Time

remembering, of course, that it will be updated every time the current
record is updated.

HTH - Keith.
www.keithwilby.com