Format Date

  • Thread starter Thread starter daver676
  • Start date Start date
D

daver676

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
 
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
Click to expand...

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
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
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.

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
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

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
Click to expand...
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
Click to expand...
that
display the
date/time field to display the date the way you want.
Click to expand...
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.
.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
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



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.
.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

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
Click to expand...
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
Click to expand...
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,
Click to expand...
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
Click to expand...
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
Click to expand...
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.
.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Take out the quote marks round the fieldname in the DateSerial()
expression, or if it's easier replace them with square brackets [].

At present the Left(), Mid() and Right() are returning characters from
the string "InvoiceDate" rather than from the field [InvoiceDate], and
DateSerial is choking on them.



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.
.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top