Transfertext enclose number fields in quotes

  • Thread starter Thread starter NancyM
  • Start date Start date
N

NancyM

I am using MS Access 2007. I have query I wish to export in csv format. I
placed the following code on the form to execute by pressing a button:

strDoc = "Sales"
strExportFile = "C:\ Documents and Settings\UserName\My
Documents\FolderName\Sales.csv
Docmd.TransferText acExportDelim, , strDoc, strExportFile, False

The resulting csv file only encloses text field in double quotes.
How do I get my number fields enclosed in double quotes? I want to use vba
code in a form so all I have to do is press a button.

Thank you for any help.
 
Nancy

The quotes serve to delimit characters/text. Why do you want quotes around
your numbers?

Assuming that you do not need to use those "numbers" as actual numbers (add,
subtract, ...), create a query and coerce those "numbers" into string
characters, then export from that query. Take a look at Access HELP for the
CStr() function.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
The csv format with quotation marks around each field is a request by a
software vendor who will be importing our data into an Oracle database. Is
there a better method?

thank you.
 
The csv format with quotation marks around each field is a request by a
software vendor who will be importing our data into an Oracle database.  Is
there a better method?

thank you.









- Show quoted text -
Click to expand...

Oracle doesn't require quotes around numbers. Dates, I think yes, but
not regular numbers.
 
Oracle may not require quotes around every field, but the developer we have
contracted with to construct a data warehouse for us has specified that EVERY
field is separated by commas and EVERY field is enclosed in quotes. I cannot
change the specs, since we are one of several companies within the
organization creating the files. I must conform to the specs.

I can change the number field to text by using Format or Cstr, the end
result I need is a text field, without commas within the field (Fixed) and
decimal precision of 2.

What can I do in a query that will turn 123.456789 into "123.46"?

Thank you for any thoughts on the matter.
 
Thank you for your suggestions. It pointed me in the direction of using
functions in the query.

I was able to find a way around the problem by using a combination of
FormatNumber to turn the number into text with 2 decimal places and then
Replace to remove the comma as a placeholder.

This satisfies the developer who insists that every field be separated by
commas and every field be enclosed in quotes.
 
Back
Top