Posted on 2013-05-17
Last Modified: 2013-05-17
Dear Experts,

I have an Access program that generates an .csv file.  The problem is, every time I export the date "2013/05/17", it gets converted to "5/17/2013".  In Access, I use Format, to force into "yyyy/mm/dd", and if I export it into .XLSX file, the format is fine.
It is when I convert it into .csv that the format change happens automatically.
Since I have to have the end result in .csv, I am not sure what I can do.
Please advise.
Question by:yballan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175274
<In Access, I use Format, to force into "yyyy/mm/dd">

try using  Cstr(Format([dateField],"yyyy/mm/dd"))
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39175453
..also, you can format it the way it should appear.
Select all, copy..
Open a new excel sheet, select all > format all cells as "text"..
Then right click Cell A1.. "paste special", and choose "Values".

Then save as csv, and it will save it the way it appears.

Author Comment

ID: 39175522
Dear Capricorn1,

Thank you for your reply, but it produced the same results.

Dear xuserx2000,

Thank you for your reply, yes, that works, but I want to do everything from Access Macro/VBA.
This will require the enduser to have to get in and out of Excel sheet.  Everything is done in one step from Access, and if I can get these formats to work, that would be ideal.
Is there a way to accomplish "format all cell as text in .csv" from the Access VBA code?
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175570
<I have an Access program that generates an .csv file.>

how do you generate the .csv file?
are you using an Export Specification for doing this?

Author Comment

ID: 39175619
Dear capricorn1,

I use TransferText from Macro, with transfer type : Export Delimited, and specify the file name "NewProducts.csv"

I tried using TransferSpreadsheet and exported to NewProducts.xlsx, which retains the proper date format.  The problem is, as soon as I save the .xlsx file to .csv from Excel, the date format changes from "yyyy/mm/dd" to "m/dd/yyyy", so that didn't help either.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175636
i have a second question in my last post..

are you using an Export Specification in your export process?

also, what version of access are you using?

Author Comment

ID: 39175733
No, I have left the "Specification Name" portion blank in my macro, so it reads like this:

TransferText  Export Delimited, ,ForExport, C:\NewProduct.csv, No, ,

I am using Access 2007.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39175840
first create an export specification

1.right click on the table export > Text file
   click on Browse and locate the destination folder
    click Save, then click OK

3. (you can accept the proposed name or change it, click OK)

4. In the export text wizard select the type (Delim / Fixed width)
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in your macro


Author Comment

ID: 39175898
Dear capricorn1,

Thank you for your detailed steps, I understand the concept, but I am having trouble being able to use the specification in my macro.
I created the text file, ExportSpec.txt,  using the wizard as you said, and corrected the descrepancies in Advanced.
Then, I Finish, the specification name is ExportSpec.txt, but I cannot select it from the Macro builder into "Specification Name".  I must be misunderstanding your direction, sorry to bug you, but what am I not doing correctly?
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 39176036
the specification Name is incorrect  "ExportSpec.txt"

save the spec as "ExportSpec"   no .txt.

see this

Author Comment

ID: 39176226
Thank you, I got it!!!!!!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question