TransferText Export Problem

[dogman_2000]

Hi All

I am using the docmd.transfertext to export my query as a .csv file.
I am using an export spec which is seperated by a pipe.

The problem I have is that I added an extra column onto the end of my
query and updated the spec.
The new column is an integer but when I export it, it always exports
as a text field i.e "100". With quotes around it.

I have tried manipulating the queries using either cDbl or NZ
functions but no luck.

I can't see anywhere in an export spec where I can change the setting
to ensure it gets exported as a number but if anyone has any ideas
please let me know.

Thanks
Andrew

[Rich P]

Hi Andrew,

Here is the easiest, most reliable way to export data to a csv file (or
text file) from Access -- use VBA.

Sub ExportDataToCSV()
Dim DB As DAO.Database, RS As DAO.Recordset, fld As DAO.Field, str1 As
String
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tbl1")
Open "c:\1A\ExportTest1.csv" For Output As #1
Do While Not RS.EOF
str1 = ""
For Each fld In RS.Fields
str1 = str1 & fld & ","
Next
str1 = Mid(str1, 1, Len(str1) - 1) '--remove last ","
Print #1, str1
RS.MoveNext
Loop
Close #1
RS.Close
End Sub
Note: the Print command in the loop (Print #1, str1) prints/writes the
data to the csv file without quotations. There is also a Write command
(Write #1, str1) which would include double quotations around the
string. In my example I am not using quotes (using Print) so I combine
all the table fields into one string. If I wanted to surround each
table field with double quotes I would use the Write Command and do each
table field individually instead of combining the fields all into one
string. This is way more reliable than using Specs and much easier to
manipulate, maintain,...

You can find more information in the Access Help Files. Just copy this
code into a standard code module. If you have any questions on anything
-- just place the mouse cursor over the object in question (say the Open
command in Open "c:\1A\ExportTest1.csv"...) and press the F1 key. That
will bring up Access help on that topic.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[dogman_2000]

On 4 Sep, 23:22, Rich P <[e-mail]:

Hi Andrew,

Here is the easiest, most reliable way to export data to a csv file (or
text file) from Access -- use VBA.

Sub ExportDataToCSV()
Dim DB As DAO.Database, RS As DAO.Recordset, fld As DAO.Field, str1 As
String
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tbl1")
Open "c:\1A\ExportTest1.csv" For Output As #1
Do While Not RS.EOF
str1 = ""
For Each fld In RS.Fields
str1 = str1 & fld & ","
Next
str1 = Mid(str1, 1, Len(str1) - 1) '--remove last ","
Print #1, str1
RS.MoveNext
Loop
Close #1
RS.Close
End Sub

Note: the Print command in the loop (Print #1, str1) prints/writes the
data to the csv file without quotations. There is also a Write command
(Write #1, str1) which would include double quotations around the
string. In my example I am not using quotes (using Print) so I combine
all the table fields into one string. If I wanted to surround each
table field with double quotes I would use the Write Command and do each
table field individually instead of combining the fields all into one
string. This is way more reliable than using Specs and much easier to
manipulate, maintain,...

You can find more information in the Access Help Files. Just copy this
code into a standard code module. If you have any questions on anything
-- just place the mouse cursor over the object in question (say the Open
command in Open "c:\1A\ExportTest1.csv"...) and press the F1 key. That
will bring up Access help on that topic.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks for the tip. Will give it a go!

I still don't understand why the field is exporting as text, but if
need be, when the file gets re-imported into access I can use a query
to change it to an integer.

Cheers