-----Original Message-----
Dear Baard:
The CDate function may be your key. First, write a simple SELECT
query on the source data and see if CDate([date]) will give a good
converted date value. If it does, just juse CDate ([date]) in the
INSERT query. If it does not, or fails even occasionally, then the
text values are not valid dates or are not being presented in a way
that can be automatically converted.
If they cannot be converted, then it would be for you to figure out
how you must process them so they can be converted. Without seeing
the data, I cannot tell what that problem might be.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Well, I get anb errormessage when the query has run
telling me that there is a 'type conversion error' (my
message is in Norwegian, but I think that is the essence
of it).
What happens is that all the fields is appended ok, except
for the date field which is left blank.
The sql looks like this:
insert into table1 (date)
select table2.date
from table2
-----Original Message-----
Dear Baard:
If by "proper date" you mean the destination is a
date/time datatype,
then the destination does not have a format. A date/time
datatype is
stored as unformatted binary data. Of course, you can
format this as
you like when you display it. It is important to think
of it in this
way. When you open the table it will be displayed using
a default
format, but do not be confused into thinking it is stored
that way.
The source for your query is, however, in text, and is
therefore of a
fixed format. Are you saying that it is not being
converted to be
stored in the destination date/time, that you are getting
an error
message? Or, are the dates there?
If the dates shown in the destination table are the
correct dates but
just not being displayed as you would like, then you have
but to
change the formatting of the display. See the Format()
function for
standard and custom date formatting. You can perform
this formatting
in a query used to display the values, in a form or
report, and you
can attach this format to the column in the table.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Mon, 8 Dec 2003 02:43:32 -0800, "Baard Dahl"
Hallo,
I am trying to append table1 with date from table2, but
I
have run into a problem. The 'date' column in table2 has
text format, but it is important that it is converted to
a
proper date format in table1.
The form of the date (text) in table 2 is yyyymmdd, and
this is the form that I want it to have in table1 as
well
(not yyyy.mm.dd or yyyy/mm/dd).
Help would be greatly appreaciated
Baard
.
.
