Convert Date from yyyy/mm/dd to mm/dd/yyyy

[Guest]

I am importing data in csv format into a table. The dates are in yyyy/mm/dd
format and I want them to be converted into mm/dd/yyyy format during the
import. Is this possible using validation and if so how is it done? If not,
do I need to import the data as text and then run a query to covert the data,
and how would that query look?

Thanks,
Perry

 

[Rick B]

Dates are not stored in the tables in a format like that. The date as saved
as a number that represents the date.

How you DISPLY the date is determined by the format you pick in your forms,
reports, and in the table. Also, your Windows REgional settings will affect
the date format in some cases.

When importing, you do not need to worry about how the date looks.

 

[Rick Brandt]

I am importing data in csv format into a table. The dates are in
yyyy/mm/dd format and I want them to be converted into mm/dd/yyyy
format during the import. Is this possible using validation and if
so how is it done? If not, do I need to import the data as text and
then run a query to covert the data, and how would that query look?

If you store them in a Date Field you don't need to convert them. Access
ALWAYS stores dates exactly the same way. Formatting is only for display.
In fact after the import you can look at the table and the dates will be
displayed according to your Windows regional settings without you doing
anything.

 

[Guest]

The only way I have been able to successfully import the data is specifying
it as a text field. I have tried Date/Time in a number of formats and it
never populates that field. I get the same results going into a new table.
Here is a sample record.
"Xxxx","Roger","X.",1945/06/15,"Male","Single","In-House",1995/07/11,"(nnn)
123-4567","226"

I have tried importing with and without a Text Qualifier ".

 

[Rick Brandt]

The only way I have been able to successfully import the data is
specifying it as a text field. I have tried Date/Time in a number of
formats and it never populates that field. I get the same results
going into a new table. Here is a sample record.
"Xxxx","Roger","X.",1945/06/15,"Male","Single","In-House",1995/07/11,"(nnn)
123-4567","226"

I have tried importing with and without a Text Qualifier ".

I don't know why it wouldn't work directly, but one often has to initially
import into a scrap table as all text and then use an append query to move
the data to the final table. The append query is then free to use whatever
conversion expressions are required to suit the final table. I would go
that route.

 

[Guest]

DIM BWDate as String
Dim NormalDate as String
' 1234567890
BWDate ="1975/01/12"
NormalDate = mid(BWdate,6,2) & "/" & mid(BWDate,9,2) & "/" & mid(BWDate,1,4)

Your data needs to be consistant. If you have "1995/1/1" then you need to
test for "/"

You can have invalid data like "1995/02/31" so you might consider leaving
the so look at the isdate function.



-


Self taught user of Access 97 – 2003 with 7 years of experience with is part
of 20 years of overall database experience. I'm still learning.

 

[Guest]

Using an append quesry did the trick, thanks!

I don't know why it wouldn't work directly, but one often has to initially
import into a scrap table as all text and then use an append query to move
the data to the final table. The append query is then free to use whatever
conversion expressions are required to suit the final table. I would go
that route.