Access97 VBA Export to CSV format issue

I have an access97 database and I am trying to write some code to export to a CSV file - (I am new to VBA).

I have this working however, there is one field that I am exporting tha开发者_如何学Pythont is a currency so in it for example is £3,456.00 - when I export to the CSV I get exactly this - however I need it to just be the number i.e 3456.00.

On a similar issue - I have the date as dd/mm/yyyy and I wonder if there is a way to convert that in VBA to yyyy-mm-dd?

Please bear in mind any solutions has to be simple due to my limited knowledge!

Sorry about the delay; seemingly easy things took longer. As I assumue from your:

DoCmd.TransferText acExportDelim, "olly_csv", "olly aorder export", "\\10.0.0.38\nw_upload\aorders.csv"

that you have an export specification "olly_csv" that determines how to export the resultset of the SELECT query "olly aorder export" to the file "aorders.csv" in the destination folder "\10.0.0.38\nw_upload".

The easy way to export the CURRENCY field(s) as plain Double/Float/Single number and the DATE field(s) with a format of your choice (dd/mm/yyyy) would be to request just that in the export specification. I found no way to do that in Access 2000 (As far as I can see, there are limited ways to pick date formats, but the features of the Import Wizard to deal with the types of columns are not implemented by the Export Wizard).

The Docs about "TransferText" (sorry, Access 2003) state:

   SpecificationName  Optional Variant. A string expression that's the name of
   an import or export specification you've created and saved in the current
   database. For a fixed-width text file, you must either specify an argument or
   use a schema.ini file, which must be stored in the same folder as the
   imported, linked, or exported text file. To create a schema file, you can use
   the text import/export wizard to create the file. For delimited text files
   and Microsoft Word mail merge data files, you can leave this argument blank
   to select the default import/export specifications.

Now there are to schools of Microsoft Docs philology: The optimists will read that as: If you don't pass an export specification and have a suitable schema.ini file, then the export process will adhere to the specs in the file. The pessimists will say: Microsoft never agreed to fullfill your pipe dreams - if you don't specify an argument for a non-fixed-width file, the TransferText command will use some obscure default export specification (please pay a consultant to seek and change it).

Let's be optimistic!

So create a schema.ini file with a section for "aorders.csv". For my tests I used a table

Tabelle: OlliesOrders 
 Name    Typ           Größe
 OrderId Long Integer   4
 Amount  Währung        8
 DateDue Datum/Uhrzeit  8

(sorry about the German; Amount is Currency, DateDue Date/Time). For that table the schema.ini section looks like:

[aorders.csv]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
DateTimeFormat=dd/mm/yyyy
Col1=OrderId Integer
Col2=Amount Float
Col3=DateDue Date

You'll have to adapt this example to your fields. Do you want column headers? Is the windows codepage ok? What about field separators? I had to use ; (German locale), you may need "Format=CSVDelimited". Look here for some background. Then call

DoCmd.TransferText acExportDelim, , "olly aorder export", "\\10.0.0.38\nw_upload\aorders.csv"

and check if optimists rule.

For pessimists:

Create a new query on the table to export (from). Change the type to Ausführung/Execute (?) and edit the SQL until it looks like:

SELECT OlliesOrders.* INTO [aorders.csv] IN 'M:\trials\23forum\SOTrials\txt' [TEXT;] FROM OlliesOrders;

resp.:

SELECT YourFieldsList INTO [aorders.csv] IN '\\10.0.0.38\nw_upload' [TEXT;] FROM YourTable;

and execute it (from the query window or a macro/module Sub). My result:

"OrderId";"Amount";"DateDue"
1;1411,09;29/04/2011
2;123,45;13/04/2011

ADDED: Evidence for my claim, that you can't specify types in the Export Wizard:

Export

Import