Date/Time datatype in Access 2003 affects date/time duplicate quer

[Tim T]

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

 

[John Spencer (MVP)]

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.

 

[Jeff Boyce]

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]

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]

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>