I'm unable to format the date fields when I export from Access 2000. I'm trying to generate a report that has to be comma delimited, strings in quotations, and date as mm/dd/yyyy. Everything looks the way it should until I export. I get mm/dd/yyyy 0:00:00 everytime.

There are several tables used in a query. The query results are saved in another table. I've tried formatting the date fields in the original tables, in the query design, and in the result table. I've tried the 'short date' format and mm/dd/yyyy in the tables, and Field1: format([date1], "mm/dd/yyyy"), Field1: left([date1], 10), and field1: format([date1], short date) in the query. All combinations return the time when I export to a text file.

The only time the zeros go away is when I export the date field as text, but then I get the quotation marks. Any ideas? Is there something I can do in Excel or a macro I can create that will format the fields as needed and generate a text file? Anything? Thanks in advance.

Recommended Answers

All 3 Replies

So, This isn't a Question about VB?

Yes, it is a question about vb. MS Office (Access databases in particular) can be used with VB 6.0. I was just looking for ideas. Sorry for bothering you.

So, This isn't a Question about VB?

No No, it's not a bother (I never intended for it to come across that way), the way the question was formed, and by your description, it seemed like more of an office issue, than a VB issue. I guess I'm not sure what you mean by "export", perhaps. So, in light of this new information, I'm going to guess that you have a VB app, that reads data from access, and puts it into excel?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.