We just converted our 97 Access databases to Access 2003. The project went
well with only some small code changes. One that has become larger over time
is...
we import XML files into the databases. In the 97 version, when the file
was imported, the Date/Time datatype field only created a time. NO date.
Which is what we wanted. Now with Acces 2003, every file imported since the
conversion has both. Some of our duplicate queries need that field with only
the time, and now with the date it gives us results we don't want.

The files are imported by a windows service we had written. The service
extracts the info from the XML and inserts it in to our databases.

Even with the the date hidden by the "Medium Time" setting in Acces 2003,
our queries still know a date is in the field and return false results!?

My question:
Does anyone know a way I can stript the date from the Date/Time datatype
field in Access 2003 before, during or after a XML file is imported by a
windows service?

Can it be done in the windows service?
Does it have to be done during the import to Access?
Is there a delete query I can run that will strip only the date & not the
time from that field??
Or am I stuck with a change due to Microsoft software?

1 developer has told me it is a Microsoft issue (no blame) but it can NOT be
done at the windows service end? It happens when the files is created in
Acces 2003??

Any and all help is appreciated.

Thank you in advance...
TT

You could use the TimeValue function in an update query.

I don't know what has caused the problem, but something like

UPDATE YourTable
SET TheTimeField = TimeValue(TheTimeField)
Where TheTimeField is Not Null

would probably fix the data for you.

Tim T wrote:
>
> We just converted our 97 Access databases to Access 2003. The project went
> well with only some small code changes. One that has become larger over time
> is...
> we import XML files into the databases. In the 97 version, when the file
> was imported, the Date/Time datatype field only created a time. NO date.
> Which is what we wanted. Now with Acces 2003, every file imported since the
> conversion has both. Some of our duplicate queries need that field with only
> the time, and now with the date it gives us results we don't want.
>
> The files are imported by a windows service we had written. The service
> extracts the info from the XML and inserts it in to our databases.
>
> Even with the the date hidden by the "Medium Time" setting in Acces 2003,
> our queries still know a date is in the field and return false results!?
>
> My question:
> Does anyone know a way I can stript the date from the Date/Time datatype
> field in Access 2003 before, during or after a XML file is imported by a
> windows service?
>
> Can it be done in the windows service?
> Does it have to be done during the import to Access?
> Is there a delete query I can run that will strip only the date & not the
> time from that field??
> Or am I stuck with a change due to Microsoft software?
>
> 1 developer has told me it is a Microsoft issue (no blame) but it can NOT be
> done at the windows service end? It happens when the files is created in
> Acces 2003??
>
> Any and all help is appreciated.
>
> Thank you in advance...
> TT

Tim

What Access stores and what it displayed are two different matters.

The Date/Time datatype stores a number representing the number of days since
some date late in the 1800's, plus a decimal value (to the right of the
decimal place) representing the decimal fraction of a date that the time
represents.

So when I just used Now() for date/time, I get 38529.56 as the actual value
stored for 6/26/05 @ approximately 1:xx pm.

First, can you confirm that your "time" values are only the decimal fraction
(e.g., ".56")?

If your field only contains ".56", are you certain it is a date/time
datatype in the table?

If you are calculating the difference between two "times", using a date/time
field, you are calculating the difference between two date/times -- whether
you are displaying Medium Time or Long Date.

If you are ABSOLUTELY certain that you only need time, I suppose you could
do a conversion that subtracts the Int() value of the date/time (38529 in
the above example) from the value in the date/time field. You would be left
with what Access will interpret as a date in the late 1800's, and a time of
.... whatever your decimal fraction works out to.

Good luck!

Jeff Boyce
<Access MVP>

"Tim T" <Tim (E-Mail Removed)> wrote in message
news:9EBC967B-5744-4FD8-A71A-(E-Mail Removed)...
> We just converted our 97 Access databases to Access 2003. The project
went
> well with only some small code changes. One that has become larger over
time
> is...
> we import XML files into the databases. In the 97 version, when the file
> was imported, the Date/Time datatype field only created a time. NO date.
> Which is what we wanted. Now with Acces 2003, every file imported since
the
> conversion has both. Some of our duplicate queries need that field with
only
> the time, and now with the date it gives us results we don't want.
>
> The files are imported by a windows service we had written. The service
> extracts the info from the XML and inserts it in to our databases.
>
> Even with the the date hidden by the "Medium Time" setting in Acces 2003,
> our queries still know a date is in the field and return false results!?
>
> My question:
> Does anyone know a way I can stript the date from the Date/Time datatype
> field in Access 2003 before, during or after a XML file is imported by a
> windows service?
>
> Can it be done in the windows service?
> Does it have to be done during the import to Access?
> Is there a delete query I can run that will strip only the date & not the
> time from that field??
> Or am I stuck with a change due to Microsoft software?
>
> 1 developer has told me it is a Microsoft issue (no blame) but it can NOT
be
> done at the windows service end? It happens when the files is created in
> Acces 2003??
>
> Any and all help is appreciated.
>
> Thank you in advance...
> TT

Jeff,

Thanks for the quick response.

The XML value imported in has only a "time" value. But when imported, the
Date/Time field in Access 2003 creates an "imported" date value in the field
with the "time" value imported from the XML? I do NOT want that date. The
"Medium Time" setting does not hide the date from my queries.

Is there a setting in Access at the table level I can stop this. How can I
fix my queries to read only the time value in that field and ignore the date??

How should I proced?

Thank again for the help.
TT

"Jeff Boyce" wrote:

> Tim
>
> What Access stores and what it displayed are two different matters.
>
> The Date/Time datatype stores a number representing the number of days since
> some date late in the 1800's, plus a decimal value (to the right of the
> decimal place) representing the decimal fraction of a date that the time
> represents.
>
> So when I just used Now() for date/time, I get 38529.56 as the actual value
> stored for 6/26/05 @ approximately 1:xx pm.
>
> First, can you confirm that your "time" values are only the decimal fraction
> (e.g., ".56")?
>
> If your field only contains ".56", are you certain it is a date/time
> datatype in the table?
>
> If you are calculating the difference between two "times", using a date/time
> field, you are calculating the difference between two date/times -- whether
> you are displaying Medium Time or Long Date.
>
> If you are ABSOLUTELY certain that you only need time, I suppose you could
> do a conversion that subtracts the Int() value of the date/time (38529 in
> the above example) from the value in the date/time field. You would be left
> with what Access will interpret as a date in the late 1800's, and a time of
> .... whatever your decimal fraction works out to.
>
> Good luck!
>
> Jeff Boyce
> <Access MVP>
>
> "Tim T" <Tim (E-Mail Removed)> wrote in message
> news:9EBC967B-5744-4FD8-A71A-(E-Mail Removed)...
> > We just converted our 97 Access databases to Access 2003. The project
> went
> > well with only some small code changes. One that has become larger over
> time
> > is...
> > we import XML files into the databases. In the 97 version, when the file
> > was imported, the Date/Time datatype field only created a time. NO date.
> > Which is what we wanted. Now with Acces 2003, every file imported since
> the
> > conversion has both. Some of our duplicate queries need that field with
> only
> > the time, and now with the date it gives us results we don't want.
> >
> > The files are imported by a windows service we had written. The service
> > extracts the info from the XML and inserts it in to our databases.
> >
> > Even with the the date hidden by the "Medium Time" setting in Acces 2003,
> > our queries still know a date is in the field and return false results!?
> >
> > My question:
> > Does anyone know a way I can stript the date from the Date/Time datatype
> > field in Access 2003 before, during or after a XML file is imported by a
> > windows service?
> >
> > Can it be done in the windows service?
> > Does it have to be done during the import to Access?
> > Is there a delete query I can run that will strip only the date & not the
> > time from that field??
> > Or am I stuck with a change due to Microsoft software?
> >
> > 1 developer has told me it is a Microsoft issue (no blame) but it can NOT
> be
> > done at the windows service end? It happens when the files is created in
> > Acces 2003??
> >
> > Any and all help is appreciated.
> >
> > Thank you in advance...
> > TT
>
>

Tm

To reiterate what many responses have told you -- Access has a Date/Time
datatype. It does not have a "Time" datatype. If you set a format to
display Medium Time, you have NOT removed the date.

If you ONLY want to work with time, use a numeric field and do the math.

Good luck

Jeff Boyce
<Access MVP>

"Tim T" <(E-Mail Removed)> wrote in message
news:617A199B-EEC4-4129-952D-(E-Mail Removed)...
> Jeff,
>
> Thanks for the quick response.
>
> The XML value imported in has only a "time" value. But when imported, the
> Date/Time field in Access 2003 creates an "imported" date value in the
field
> with the "time" value imported from the XML? I do NOT want that date.
The
> "Medium Time" setting does not hide the date from my queries.
>
> Is there a setting in Access at the table level I can stop this. How can
I
> fix my queries to read only the time value in that field and ignore the
date??
>
> How should I proced?
>
> Thank again for the help.
> TT
>
> "Jeff Boyce" wrote:
>
> > Tim
> >
> > What Access stores and what it displayed are two different matters.
> >
> > The Date/Time datatype stores a number representing the number of days
since
> > some date late in the 1800's, plus a decimal value (to the right of the
> > decimal place) representing the decimal fraction of a date that the time
> > represents.
> >
> > So when I just used Now() for date/time, I get 38529.56 as the actual
value
> > stored for 6/26/05 @ approximately 1:xx pm.
> >
> > First, can you confirm that your "time" values are only the decimal
fraction
> > (e.g., ".56")?
> >
> > If your field only contains ".56", are you certain it is a date/time
> > datatype in the table?
> >
> > If you are calculating the difference between two "times", using a
date/time
> > field, you are calculating the difference between two date/times --
whether
> > you are displaying Medium Time or Long Date.
> >
> > If you are ABSOLUTELY certain that you only need time, I suppose you
could
> > do a conversion that subtracts the Int() value of the date/time (38529
in
> > the above example) from the value in the date/time field. You would be
left
> > with what Access will interpret as a date in the late 1800's, and a time
of
> > .... whatever your decimal fraction works out to.
> >
> > Good luck!
> >
> > Jeff Boyce
> > <Access MVP>
> >
> > "Tim T" <Tim (E-Mail Removed)> wrote in message
> > news:9EBC967B-5744-4FD8-A71A-(E-Mail Removed)...
> > > We just converted our 97 Access databases to Access 2003. The project
> > went
> > > well with only some small code changes. One that has become larger
over
> > time
> > > is...
> > > we import XML files into the databases. In the 97 version, when the
file
> > > was imported, the Date/Time datatype field only created a time. NO
date.
> > > Which is what we wanted. Now with Acces 2003, every file imported
since
> > the
> > > conversion has both. Some of our duplicate queries need that field
with
> > only
> > > the time, and now with the date it gives us results we don't want.
> > >
> > > The files are imported by a windows service we had written. The
service
> > > extracts the info from the XML and inserts it in to our databases.
> > >
> > > Even with the the date hidden by the "Medium Time" setting in Acces
2003,
> > > our queries still know a date is in the field and return false
results!?
> > >
> > > My question:
> > > Does anyone know a way I can stript the date from the Date/Time
datatype
> > > field in Access 2003 before, during or after a XML file is imported by
a
> > > windows service?
> > >
> > > Can it be done in the windows service?
> > > Does it have to be done during the import to Access?
> > > Is there a delete query I can run that will strip only the date & not
the
> > > time from that field??
> > > Or am I stuck with a change due to Microsoft software?
> > >
> > > 1 developer has told me it is a Microsoft issue (no blame) but it can
NOT
> > be
> > > done at the windows service end? It happens when the files is created
in
> > > Acces 2003??
> > >
> > > Any and all help is appreciated.
> > >
> > > Thank you in advance...
> > > TT
> >
> >