import spec: errror when importing date

My date is in this format "YYYYMMDD" and when i import it i get the error: "Type Conversion Failure"
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
if it's a char field in your source, then in your Access database, try to make it as a Char field as well. After the importing, you can do the necessary updates to convert the field from char to a proper date.

OR you can format the date in the source as a proper formatted date field, so we can try to avoid any conversion errors.
0
vbnetcoderAuthor Commented:
But this is a valid date format right? I don't think i should have to make it into a char field
0
Ryan ChongCommented:
>>But this is a valid date format right?
"YYYYMMDD" is not a valid date value (that's why you hitting conversion error, I assume your target table's field is a date field?), but "YYYY-MM-DD", "DD/MM/YYYY" or "DD MMM YYYY", etc is.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
change the Data Type of the destination field to Text in your import Specification
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
for my purposed i decided it would work fine to make date fields text
0
PatHartmanCommented:
I see that you have marked the question answered but you never got an accurate answer.  Yes, you can get Access to import this field as a date.  You just need to know how.

When you are using the import wizard, you can define this date format and Access will import it as a proper date field.  At any point in the wizard, click on the advanced button.  You will see options to choose a data field order.  Choose YMD.  You also have options that specify delimiters or no delimiters and leading zeros or not.  Choose no delimiters and leading zeros.  Then specify that column as a date.  Save the import spec and name it before clicking the finish button.  That way you can use the import spec in the TransferText method to automate the import.
0
Rey Obrero (Capricorn1)Commented:
<When you are using the import wizard, you can define this date format and Access will import it as a proper date field.>

NOT with data written  "YYYYMMDD"
0
PatHartmanCommented:
Yes, you can.  I do it with files I get from a vendor.  The date actually comes to me as mmddyyyy but it doesn't matter since you can specify the field order.  

I included a sample .csv file in the import.  Use the import spec I created.
ImportFlatDate.zip
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.