I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD". How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
-----Original Message-----
Import it as is, then add a date/time field to the table. Use an update
query with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the text date into an
Access date/time value. Finally format the controls that display the
date/time field to display the date the way you want.
I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD". How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
I don't understand...
Does the line you gave me go into the Update line in the
Update Query? What is the format line and where should it
go? What should be in the criteria line in the query? does
is Date Serial line make the data type in the table a
date/time field? I guess what I'm saying is, could you be
more specific with your answer please?
Dave-----Original Message-----
Import it as is, then add a date/time field to the table. Use an update
query with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the text date into an
Access date/time value. Finally format the controls that display the
date/time field to display the date the way you want.
I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD". How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
-----Original Message-----
First, import the data as is. In the text import wizard, make sure that
the field with the YYMMDD date is imported as a text field. I'll assume
it's called "F" and that you call the table you're importing it to "T".
Having imported the data, open the table in design view and add a
Date/Time field. I'll assume you call it "D". In the format property of
the field, put
mm/dd/yyyy
Then create the update query. Start by creating a new query in design
view, based on table T. Put just one field in the query, the new
date/time field D. From the Query menu, select Update Query.
Leave the criteria row empty, because you are going to update all the
records.
Put an expression like this (adjusting the field name, of course) in the
Update To cell:
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
The DateSerial function just takes the pieces of the date and converts
them into an Access date/time value. It doesn't affect the data type of
the field or the way it's displayed.
When you put a control on a form to display the data in field D you may
need to set the Format property of the control to
mm/dd/yyyy
I don't understand...
Does the line you gave me go into the Update line in the
Update Query? What is the format line and where should it
go? What should be in the criteria line in the query? does
is Date Serial line make the data type in the table a
date/time field? I guess what I'm saying is, could you be
more specific with your answer please?
Davetable.-----Original Message-----
Import it as is, then add a date/time field to the
Use an updatethatquery with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the text date into an
Access date/time value. Finally format the controls
display thedate/time field to display the date the way you want.wrote:
I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD". How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
This gives me an error. Data type mismatch in criteria
expression. error 3464. There is nothing in the criteria
field in the query. Whats the problem here?-----Original Message-----
First, import the data as is. In the text import wizard, make sure that
the field with the YYMMDD date is imported as a text field. I'll assume
it's called "F" and that you call the table you're importing it to "T".
Having imported the data, open the table in design view and add a
Date/Time field. I'll assume you call it "D". In the format property of
the field, put
mm/dd/yyyy
Then create the update query. Start by creating a new query in design
view, based on table T. Put just one field in the query, the new
date/time field D. From the Query menu, select Update Query.
Leave the criteria row empty, because you are going to update all the
records.
Put an expression like this (adjusting the field name, of course) in the
Update To cell:
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
The DateSerial function just takes the pieces of the date and converts
them into an Access date/time value. It doesn't affect the data type of
the field or the way it's displayed.
When you put a control on a form to display the data in field D you may
need to set the Format property of the control to
mm/dd/yyyy
I don't understand...
Does the line you gave me go into the Update line in the
Update Query? What is the format line and where should it
go? What should be in the criteria line in the query? does
is Date Serial line make the data type in the table a
date/time field? I guess what I'm saying is, could you be
more specific with your answer please?
Dave
-----Original Message-----
Import it as is, then add a date/time field to the table.
Use an update
query with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the
text date into an
Access date/time value. Finally format the controls that
display the
date/time field to display the date the way you want.
On Wed, 25 Jun 2003 12:32:47 -0700, "daver676"
<[email protected]>
wrote:
I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD".
How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
-----Original Message-----
Please post the SQL of the update query, and also confirm that the
YYMMDD field has been imported to a text field.
This gives me an error. Data type mismatch in criteria
expression. error 3464. There is nothing in the criteria
field in the query. Whats the problem here?wizard,-----Original Message-----
First, import the data as is. In the text import
make sure thatquery,the field with the YYMMDD date is imported as a text field. I'll assume
it's called "F" and that you call the table you're importing it to "T".
Having imported the data, open the table in design view and add a
Date/Time field. I'll assume you call it "D". In the format property of
the field, put
mm/dd/yyyy
Then create the update query. Start by creating a new query in design
view, based on table T. Put just one field in the
the newofdate/time field D. From the Query menu, select Update Query.
Leave the criteria row empty, because you are going to update all the
records.
Put an expression like this (adjusting the field name,
course) in thedateUpdate To cell:
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
The DateSerial function just takes the pieces of the
and convertsthem into an Access date/time value. It doesn't affect the data type of
the field or the way it's displayed.
When you put a control on a form to display the data in field D you may
need to set the Format property of the control to
mm/dd/yyyywrote:
I don't understand...
Does the line you gave me go into the Update line in the
Update Query? What is the format line and where should it
go? What should be in the criteria line in the query? does
is Date Serial line make the data type in the table a
date/time field? I guess what I'm saying is, could you be
more specific with your answer please?
Dave
-----Original Message-----
Import it as is, then add a date/time field to the table.
Use an update
query with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the
text date into an
Access date/time value. Finally format the controls that
display the
date/time field to display the date the way you want.
On Wed, 25 Jun 2003 12:32:47 -0700, "daver676"
<[email protected]>
wrote:
I'm importing a text file into a db table using a macro.
The format of the date from the text file is "YYMMDD".
How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
Sql:
UPDATE ARINV SET ARINV.InvoiceDate1 = DateSerial(Left
("InvoiceDate",2),Mid("InvoiceDate",3,2),Right
("InvoiceDate",2));
Original Field named "Invoicedate", new formatted field
named "InvoiceDate1"
Data Type for original transfer IS text.
-----Original Message-----
Please post the SQL of the update query, and also confirm that the
YYMMDD field has been imported to a text field.
This gives me an error. Data type mismatch in criteria
expression. error 3464. There is nothing in the criteria
field in the query. Whats the problem here?
-----Original Message-----
First, import the data as is. In the text import wizard,
make sure that
the field with the YYMMDD date is imported as a text
field. I'll assume
it's called "F" and that you call the table you're
importing it to "T".
Having imported the data, open the table in design view
and add a
Date/Time field. I'll assume you call it "D". In the
format property of
the field, put
mm/dd/yyyy
Then create the update query. Start by creating a new
query in design
view, based on table T. Put just one field in the query,
the new
date/time field D. From the Query menu, select Update
Query.
Leave the criteria row empty, because you are going to
update all the
records.
Put an expression like this (adjusting the field name, of
course) in the
Update To cell:
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
The DateSerial function just takes the pieces of the date
and converts
them into an Access date/time value. It doesn't affect
the data type of
the field or the way it's displayed.
When you put a control on a form to display the data in
field D you may
need to set the Format property of the control to
mm/dd/yyyy
On Thu, 26 Jun 2003 06:53:53 -0700, "daver676"
<[email protected]>
wrote:
I don't understand...
Does the line you gave me go into the Update line in the
Update Query? What is the format line and where should
it
go? What should be in the criteria line in the query?
does
is Date Serial line make the data type in the table a
date/time field? I guess what I'm saying is, could you
be
more specific with your answer please?
Dave
-----Original Message-----
Import it as is, then add a date/time field to the
table.
Use an update
query with an expression like
DateSerial(Left(F, 2), Mid(F, 3, 2), Right(F, 2))
(where F is the name of the text field) to convert the
text date into an
Access date/time value. Finally format the controls
that
display the
date/time field to display the date the way you want.
On Wed, 25 Jun 2003 12:32:47 -0700, "daver676"
<[email protected]>
wrote:
I'm importing a text file into a db table using a
macro.
The format of the date from the text file is "YYMMDD".
How
to I convert this to "MM/DD/YYYY" in Access?
Thanks
Dave
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.