Date/Time datatype affects date/time query

[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

 

[Brendan Reynolds]

A Jet Date field always includes both date and time, Tim, and it always has.
There is no change in the way this works in Jet 3.5 (Access 97) and Jet 3.6
(Access 2000, 2002, and 2003). If no date part is specified, the date part
will default to 30 December, 1899. You could run an update query to change
the date part to that date, so that all records would have the same date
part. Or you could re-write your existing queries to ignore the date part.
But I think your developer friend is mistaken, I see no reason why the
problem could not be handled in the service.

If you go with the update query, it would look something like this ...

UPDATE YourTable SET YourDateField = TimeSerial(Hour(YourDateField),
Minute(YourDateField), Second(YourDateField));

 

[Tim T]

Brendan,

Thank you for the quick reply.

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 you again... as you can see I really need the help.
TT

 

[Rick Brandt]

Brendan,

Thank you for the quick reply.

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 you again... as you can see I really need the help.
TT

What is the date you are seeing? If it is 12/30/1899 then this is completely
normal. An Access DateTime CANNOT have time only. Normally the display of
12/30/1899 is suppressed in datasheets but if you export the data or apply a
format that shows a date then 12/30/1899 will appear in those "time only"
fields. This is similar to the way the display of midnight is suppressed in
"date only" fields. There is always both a date and time stored.

If you are seeing some other date besides 12/30/1899 then that would be
something to investigate further.

 

[Pieter Wijnen]

Simplified Truncation of DatePart
UPDATE TheTable Set DATEFIELD = DATEFIELD - Clng(DATEFIELD)
WHERE DATEFIELD >=1

Query "Field" ignoring Date
TimePart: DATEFIELD - Clng(DATEFIELD)

HTH

Pieter

 

[Tim T]

Pieter,

thank you...

Where and how do i use the Query field command.

I've tried a few ways and I can' get it to go?

TIA
TT