I have a date field that currently has only the month, day and year. I would
like to add the time to it as well. The input to the append does not have
the time. Can I format a time and have it as part of the date field?

Thanks

"Bunky" <(E-Mail Removed)> wrote in message
news:C4CBD02C-FEBC-4395-B14E-(E-Mail Removed)...
>I have a date field that currently has only the month, day and year. I
>would
> like to add the time to it as well. The input to the append does not have
> the time. Can I format a time and have it as part of the date field?


I'm not entirely sure what you have to work with here. It may help to know
that both date and time values are stored in the same specialized
floating-point format -- date values as the integer part, time values as the
fractional part -- so for dates since December 30, 1899 (the "zero" date in
this structure), you can just add time values to date values to get
date/time values. I'm not sure if this works with dates before the zero
date.

Lets suppose that you have two tables, one with the date values and one with
the time values, which share a primary key. Further suppose that both the
date fields and the time fields are of the date/time field type. For
example, suppose you have these tables:

MyDateTable
-------------------
ID (primary key)
DateField (type Date, currently contains only dates)

MyTimeTable
-------------------
ID (primary key, fk to MyDate.ID)
TimeField (type Date, currently contains only times)

Further suppose that all these dates are since 12/30/1899. Then you could
update the date fields in MyDateTable to include the times as well using a
query like this:

UPDATE
MyDateTable INNER JOIN MyTimeTable
ON MyDateTable.ID = MyTimeTable.ID
SET MyDateTable.DateField =
MyDateTable.DateField + MyTimeTable.TimeField

I haven't tested it, but something along those lines ought to work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi

Before you do anything try opening the table in design and change the format
of the field to General Date.
You may alrady have the details you need.

--
Wayne
Manchester, England.



"Bunky" wrote:

> I have a date field that currently has only the month, day and year. I would
> like to add the time to it as well. The input to the append does not have
> the time. Can I format a time and have it as part of the date field?
>
> Thanks

I've tried this but if the input does not have the date, it does not put a
date in during the append.

"Wayne-I-M" wrote:

> Hi
>
> Before you do anything try opening the table in design and change the format
> of the field to General Date.
> You may alrady have the details you need.
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Bunky" wrote:
>
> > I have a date field that currently has only the month, day and year. I would
> > like to add the time to it as well. The input to the append does not have
> > the time. Can I format a time and have it as part of the date field?
> >
> > Thanks

What I am working with is one table that has a general date defined in a
date/time field. This table has a date in the field - no time. When I
append this data to another table, I would like to have it include a time
with this date. Is this possible? If so How????


"Dirk Goldgar" wrote:

> "Bunky" <(E-Mail Removed)> wrote in message
> news:C4CBD02C-FEBC-4395-B14E-(E-Mail Removed)...
> >I have a date field that currently has only the month, day and year. I
> >would
> > like to add the time to it as well. The input to the append does not have
> > the time. Can I format a time and have it as part of the date field?
>
>
> I'm not entirely sure what you have to work with here. It may help to know
> that both date and time values are stored in the same specialized
> floating-point format -- date values as the integer part, time values as the
> fractional part -- so for dates since December 30, 1899 (the "zero" date in
> this structure), you can just add time values to date values to get
> date/time values. I'm not sure if this works with dates before the zero
> date.
>
> Lets suppose that you have two tables, one with the date values and one with
> the time values, which share a primary key. Further suppose that both the
> date fields and the time fields are of the date/time field type. For
> example, suppose you have these tables:
>
> MyDateTable
> -------------------
> ID (primary key)
> DateField (type Date, currently contains only dates)
>
> MyTimeTable
> -------------------
> ID (primary key, fk to MyDate.ID)
> TimeField (type Date, currently contains only times)
>
> Further suppose that all these dates are since 12/30/1899. Then you could
> update the date fields in MyDateTable to include the times as well using a
> query like this:
>
> UPDATE
> MyDateTable INNER JOIN MyTimeTable
> ON MyDateTable.ID = MyTimeTable.ID
> SET MyDateTable.DateField =
> MyDateTable.DateField + MyTimeTable.TimeField
>
> I haven't tested it, but something along those lines ought to work.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

You can use an append with dateadd (if you have the date) - but it would seem
(to me that unless (as Dirk says) you actually have the time in a table - how
will you know what time to add.

Sorry I'm not understand this
--
Wayne
Manchester, England.



"Bunky" wrote:

> What I am working with is one table that has a general date defined in a
> date/time field. This table has a date in the field - no time. When I
> append this data to another table, I would like to have it include a time
> with this date. Is this possible? If so How????
>
>
> "Dirk Goldgar" wrote:
>
> > "Bunky" <(E-Mail Removed)> wrote in message
> > news:C4CBD02C-FEBC-4395-B14E-(E-Mail Removed)...
> > >I have a date field that currently has only the month, day and year. I
> > >would
> > > like to add the time to it as well. The input to the append does not have
> > > the time. Can I format a time and have it as part of the date field?
> >
> >
> > I'm not entirely sure what you have to work with here. It may help to know
> > that both date and time values are stored in the same specialized
> > floating-point format -- date values as the integer part, time values as the
> > fractional part -- so for dates since December 30, 1899 (the "zero" date in
> > this structure), you can just add time values to date values to get
> > date/time values. I'm not sure if this works with dates before the zero
> > date.
> >
> > Lets suppose that you have two tables, one with the date values and one with
> > the time values, which share a primary key. Further suppose that both the
> > date fields and the time fields are of the date/time field type. For
> > example, suppose you have these tables:
> >
> > MyDateTable
> > -------------------
> > ID (primary key)
> > DateField (type Date, currently contains only dates)
> >
> > MyTimeTable
> > -------------------
> > ID (primary key, fk to MyDate.ID)
> > TimeField (type Date, currently contains only times)
> >
> > Further suppose that all these dates are since 12/30/1899. Then you could
> > update the date fields in MyDateTable to include the times as well using a
> > query like this:
> >
> > UPDATE
> > MyDateTable INNER JOIN MyTimeTable
> > ON MyDateTable.ID = MyTimeTable.ID
> > SET MyDateTable.DateField =
> > MyDateTable.DateField + MyTimeTable.TimeField
> >
> > I haven't tested it, but something along those lines ought to work.
> >
> > --
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com
> >
> > (please reply to the newsgroup)
> >

I would like to just slug a time in (12:00:00 AM) comes to mind. What would
be the syntax to do that with the date that is a good date?

"Wayne-I-M" wrote:

> You can use an append with dateadd (if you have the date) - but it would seem
> (to me that unless (as Dirk says) you actually have the time in a table - how
> will you know what time to add.
>
> Sorry I'm not understand this
> --
> Wayne
> Manchester, England.
>
>
>
> "Bunky" wrote:
>
> > What I am working with is one table that has a general date defined in a
> > date/time field. This table has a date in the field - no time. When I
> > append this data to another table, I would like to have it include a time
> > with this date. Is this possible? If so How????
> >
> >
> > "Dirk Goldgar" wrote:
> >
> > > "Bunky" <(E-Mail Removed)> wrote in message
> > > news:C4CBD02C-FEBC-4395-B14E-(E-Mail Removed)...
> > > >I have a date field that currently has only the month, day and year. I
> > > >would
> > > > like to add the time to it as well. The input to the append does not have
> > > > the time. Can I format a time and have it as part of the date field?
> > >
> > >
> > > I'm not entirely sure what you have to work with here. It may help to know
> > > that both date and time values are stored in the same specialized
> > > floating-point format -- date values as the integer part, time values as the
> > > fractional part -- so for dates since December 30, 1899 (the "zero" date in
> > > this structure), you can just add time values to date values to get
> > > date/time values. I'm not sure if this works with dates before the zero
> > > date.
> > >
> > > Lets suppose that you have two tables, one with the date values and one with
> > > the time values, which share a primary key. Further suppose that both the
> > > date fields and the time fields are of the date/time field type. For
> > > example, suppose you have these tables:
> > >
> > > MyDateTable
> > > -------------------
> > > ID (primary key)
> > > DateField (type Date, currently contains only dates)
> > >
> > > MyTimeTable
> > > -------------------
> > > ID (primary key, fk to MyDate.ID)
> > > TimeField (type Date, currently contains only times)
> > >
> > > Further suppose that all these dates are since 12/30/1899. Then you could
> > > update the date fields in MyDateTable to include the times as well using a
> > > query like this:
> > >
> > > UPDATE
> > > MyDateTable INNER JOIN MyTimeTable
> > > ON MyDateTable.ID = MyTimeTable.ID
> > > SET MyDateTable.DateField =
> > > MyDateTable.DateField + MyTimeTable.TimeField
> > >
> > > I haven't tested it, but something along those lines ought to work.
> > >
> > > --
> > > Dirk Goldgar, MS Access MVP
> > > www.datagnostics.com
> > >
> > > (please reply to the newsgroup)
> > >

"Bunky" <(E-Mail Removed)> wrote in message
news:0659FC91-F1A5-49BE-904F-(E-Mail Removed)...
>I would like to just slug a time in (12:00:00 AM) comes to mind. What
>would
> be the syntax to do that with the date that is a good date?


Bunky, *every* date field has a time portion that is part of it. If that
time was never set, the time is 12:00:00 AM. It purely a matter of the
format applied to the field that determines whether you see it when you open
the table or not. By default, a date a time of 12:00:00 AM, displayed using
the "general date" format, will not show the time, because it assumes that
you don't care about it. If you want to show the time always, even if it's
12:00 AM, then use a format such as

m/d/yyyy h:nn:ss AMPM

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I'm still lost with this but .....

You could use something like this to hange a field based on the date
contained within the table

UPDATE SomeTable SET SomeTable.NewTimeField =
Format(SomeTable!DateField,"General Date")
WHERE ((Not (SomeTable.DateField) Is Null));

Change the names
Note - I assume you have a date/time field called DateField and you want to
insert this information into a field called NewTimeField formated GeneralDate
(this will give you the 00:00am you want. The table is called SomeTable

Good luck


--
Wayne
Manchester, England.



"Bunky" wrote:

> I would like to just slug a time in (12:00:00 AM) comes to mind. What would
> be the syntax to do that with the date that is a good date?
>
> "Wayne-I-M" wrote:
>
> > You can use an append with dateadd (if you have the date) - but it would seem
> > (to me that unless (as Dirk says) you actually have the time in a table - how
> > will you know what time to add.
> >
> > Sorry I'm not understand this
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "Bunky" wrote:
> >
> > > What I am working with is one table that has a general date defined in a
> > > date/time field. This table has a date in the field - no time. When I
> > > append this data to another table, I would like to have it include a time
> > > with this date. Is this possible? If so How????
> > >
> > >
> > > "Dirk Goldgar" wrote:
> > >
> > > > "Bunky" <(E-Mail Removed)> wrote in message
> > > > news:C4CBD02C-FEBC-4395-B14E-(E-Mail Removed)...
> > > > >I have a date field that currently has only the month, day and year. I
> > > > >would
> > > > > like to add the time to it as well. The input to the append does not have
> > > > > the time. Can I format a time and have it as part of the date field?
> > > >
> > > >
> > > > I'm not entirely sure what you have to work with here. It may help to know
> > > > that both date and time values are stored in the same specialized
> > > > floating-point format -- date values as the integer part, time values as the
> > > > fractional part -- so for dates since December 30, 1899 (the "zero" date in
> > > > this structure), you can just add time values to date values to get
> > > > date/time values. I'm not sure if this works with dates before the zero
> > > > date.
> > > >
> > > > Lets suppose that you have two tables, one with the date values and one with
> > > > the time values, which share a primary key. Further suppose that both the
> > > > date fields and the time fields are of the date/time field type. For
> > > > example, suppose you have these tables:
> > > >
> > > > MyDateTable
> > > > -------------------
> > > > ID (primary key)
> > > > DateField (type Date, currently contains only dates)
> > > >
> > > > MyTimeTable
> > > > -------------------
> > > > ID (primary key, fk to MyDate.ID)
> > > > TimeField (type Date, currently contains only times)
> > > >
> > > > Further suppose that all these dates are since 12/30/1899. Then you could
> > > > update the date fields in MyDateTable to include the times as well using a
> > > > query like this:
> > > >
> > > > UPDATE
> > > > MyDateTable INNER JOIN MyTimeTable
> > > > ON MyDateTable.ID = MyTimeTable.ID
> > > > SET MyDateTable.DateField =
> > > > MyDateTable.DateField + MyTimeTable.TimeField
> > > >
> > > > I haven't tested it, but something along those lines ought to work.
> > > >
> > > > --
> > > > Dirk Goldgar, MS Access MVP
> > > > www.datagnostics.com
> > > >
> > > > (please reply to the newsgroup)
> > > >

Dick,

Thank you so much! I did not know the time is there always. It solves my
problem totally.

Kent

"Dirk Goldgar" wrote:

> "Bunky" <(E-Mail Removed)> wrote in message
> news:0659FC91-F1A5-49BE-904F-(E-Mail Removed)...
> >I would like to just slug a time in (12:00:00 AM) comes to mind. What
> >would
> > be the syntax to do that with the date that is a good date?
>
>
> Bunky, *every* date field has a time portion that is part of it. If that
> time was never set, the time is 12:00:00 AM. It purely a matter of the
> format applied to the field that determines whether you see it when you open
> the table or not. By default, a date a time of 12:00:00 AM, displayed using
> the "general date" format, will not show the time, because it assumes that
> you don't care about it. If you want to show the time always, even if it's
> 12:00 AM, then use a format such as
>
> m/d/yyyy h:nn:ss AMPM
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>