exporting query to text file [help!]

[alessia]

hi,
I have a query that visualizes me, according to some criterions from
me inserted, name , Surname , Address , phone
ok..

Now, through a button, positioned in a Form,
I have to only export from this query the field telephone in a text
file
the name of the text file I would like to choose me...

the text file example:

345368998756
345894614542
168416616464
165445648250

they will have to appear a telephone number on every line without any
; or "
as in the example that I have done here above..
can someone help me? some example of code?
thanks
alexia
i'm access newbie.....
[I am desperate]

[Fletcher Arnold]

"alessia" <al*************@yahoo.it> wrote in message
news:gv********************************@4ax.com...

hi,
I have a query that visualizes me, according to some criterions from
me inserted, name , Surname , Address , phone
ok..

Now, through a button, positioned in a Form,
I have to only export from this query the field telephone in a text
file
the name of the text file I would like to choose me...

the text file example:

345368998756
345894614542
168416616464
165445648250

they will have to appear a telephone number on every line without any
; or "
as in the example that I have done here above..
can someone help me? some example of code?
thanks
alexia
i'm access newbie.....
[I am desperate]

Create a new query "qryExport" which is based on your first query but
selects only the phone number. Then, if the button on your form is named
'cmdExport' the code might be as shown below. Note that if you need a
better way to select the export file path, then there is code for this too.

Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

DoCmd.TransferText acExportDelim, , "qryExport", strPath

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[alessia]

Il Fri, 10 Oct 2003 08:06:24 +0000 (UTC), "Fletcher Arnold"
<fl****@home.com> ha scritto:

"alessia" <al*************@yahoo.it> wrote in message
news:gv********************************@4ax.com.. .

hi,
I have a query that visualizes me, according to some criterions from
me inserted, name , Surname , Address , phone
ok..

Now, through a button, positioned in a Form,
I have to only export from this query the field telephone in a text
file
the name of the text file I would like to choose me...

the text file example:

345368998756
345894614542
168416616464
165445648250

they will have to appear a telephone number on every line without any
; or "
as in the example that I have done here above..
can someone help me? some example of code?
thanks
alexia
i'm access newbie.....
[I am desperate]

Create a new query "qryExport" which is based on your first query but
selects only the phone number. Then, if the button on your form is named
'cmdExport' the code might be as shown below. Note that if you need a
better way to select the export file path, then there is code for this too.

Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

DoCmd.TransferText acExportDelim, , "qryExport", strPath

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

thanks! you are kind.. I immediately try the code that you have given
me...
it excuses my bad English... I am Italian
good day

[alessia]

Il Sat, 11 Oct 2003 08:48:59 GMT, alessia <al*************@yahoo.it>
ha scritto:

Create a new query "qryExport" which is based on your first query but
selects only the phone number. Then, if the button on your form is named
'cmdExport' the code might be as shown below. Note that if you need a
better way to select the export file path, then there is code for this too.

Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

DoCmd.TransferText acExportDelim, , "qryExport", strPath

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

OK, work! great! thanks Fletcher!

however when I open the created text file
the telephone numbers come me this way

"3332321415416385"
"5156468481565484"

how do I do for eliminating the " "??

I would like only the telephone number in this way

1515645166544
1454468468154

have a good day!

[Fletcher Arnold]

"alessia" <al*************@yahoo.it> wrote in message
news:k2********************************@4ax.com...

Il Sat, 11 Oct 2003 08:48:59 GMT, alessia <al*************@yahoo.it>
ha scritto:

Create a new query "qryExport" which is based on your first query but
selects only the phone number. Then, if the button on your form is named'cmdExport' the code might be as shown below. Note that if you need a
better way to select the export file path, then there is code for this too.
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String

strPath = InputBox("Enter file path", , "C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

DoCmd.TransferText acExportDelim, , "qryExport", strPath

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

OK, work! great! thanks Fletcher!

however when I open the created text file
the telephone numbers come me this way

"3332321415416385"
"5156468481565484"

how do I do for eliminating the " "??

I would like only the telephone number in this way

1515645166544
1454468468154

have a good day!

There are a number of ways to do this. Perhaps the easiest is:

1. Select qryExport in the database window.
2. From the menu at the top choose File>Export
3. Select filename and type = text then press Export
4. On this screen you can press "Advanced"
5. Set up your options here - especially change text qualifier from " to
none
6. Choose Save as ..."QryExport Export Specification"
7. Finish export

You can now change the code from:
DoCmd.TransferText acExportDelim, , "qryExport", strPath
to:
DoCmd.TransferText acExportDelim, "QryExport Export Specification",
"qryExport", strPath
If this is too difficult I could write some code to do this for you.
Fletcher

[alessia]

Il Sat, 11 Oct 2003 12:19:58 +0000 (UTC), "Fletcher Arnold"
<fl****@home.com> ha scritto:

"alessia" <al*************@yahoo.it> wrote in message
news:k2********************************@4ax.com.. .

Il Sat, 11 Oct 2003 08:48:59 GMT, alessia <al*************@yahoo.it>
ha scritto:

>>Create a new query "qryExport" which is based on your first query but
>>selects only the phone number. Then, if the button on your form isnamed >>'cmdExport' the code might be as shown below. Note that if you need a
>>better way to select the export file path, then there is code for thistoo. >>
>>Private Sub cmdExport_Click()
>>
>> On Error GoTo Err_Handler
>>
>> Dim strPath As String
>>
>> strPath = InputBox("Enter file path", , "C:\Shared\Test.txt")
>>
>> If Len(strPath) = 0 Then Exit Sub
>>
>> DoCmd.TransferText acExportDelim, , "qryExport", strPath
>>
>> MsgBox "Done", vbInformation
>>
>>Exit_Handler:
>> Exit Sub
>>
>>Err_Handler:
>> MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
>> Resume Exit_Handler
>>
>>End Sub
>>

OK, work! great! thanks Fletcher!

however when I open the created text file
the telephone numbers come me this way

"3332321415416385"
"5156468481565484"

how do I do for eliminating the " "??

I would like only the telephone number in this way

1515645166544
1454468468154

have a good day!

There are a number of ways to do this. Perhaps the easiest is:

1. Select qryExport in the database window.
2. From the menu at the top choose File>Export
3. Select filename and type = text then press Export
4. On this screen you can press "Advanced"
5. Set up your options here - especially change text qualifier from " to
none
6. Choose Save as ..."QryExport Export Specification"
7. Finish export

You can now change the code from:
DoCmd.TransferText acExportDelim, , "qryExport", strPath
to:
DoCmd.TransferText acExportDelim, "QryExport Export Specification",
"qryExport", strPath
If this is too difficult I could write some code to do this for you.
Fletcher

thanks Fletcher! very much!

have a nice day!

Alessia.

[alessia]

Il Sat, 11 Oct 2003 22:50:16 GMT, alessia <al*************@yahoo.it>
ha scritto:

If this is too difficult I could write some code to do this for you.

I have tried what you have suggested me you, Fletcher, but I would
prefer to serve everything as code... I would not like to disturb too
much, and if you have of the of leisure time to write the code, I
would be thankful of it... if it doesn't find you possible...don't
worry you...I thank you... you have been kind indeed.
[i'm sorry my bad english...I hope is understood ]

alessia

[Fletcher Arnold]

"alessia" <al*************@yahoo.it> wrote in message
news:ti********************************@4ax.com...

Il Sat, 11 Oct 2003 22:50:16 GMT, alessia <al*************@yahoo.it>
ha scritto:

If this is too difficult I could write some code to do this for you.

I have tried what you have suggested me you, Fletcher, but I would
prefer to serve everything as code... I would not like to disturb too
much, and if you have of the of leisure time to write the code, I
would be thankful of it... if it doesn't find you possible...don't
worry you...I thank you... you have been kind indeed.
[i'm sorry my bad english...I hope is understood ]

alessia

Hi Alessia
I am glad that you got the code to work. Don't worry about your English -
its fine, and very polite!
The following code is one possible version. It assumes your query is
"qryExport" which contains the field "Tel" - if not change the lines:
Set rst = dbs.OpenRecordset("qryExport" ...
and
Print #intFile, Nz(rst!Tel, "")

When you paste the new code in, make sure you compile the code -
Debug>Compile. If it does not compile, check Tools>References and make sure
the box Microsoft DAO 3.6 Object Library is checked.

Good Luck

Fletcher
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String
Dim intFile As Integer
Dim strLine As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strPath = InputBox("Enter file path", _
"Export", _
"C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

If Len(Dir(strPath)) > 0 Then
If MsgBox("This file already exists" & vbCrLf & _
"Would you like to overwrite it?", _
vbExclamation Or vbYesNoCancel, _
"Export") <> vbYes Then Exit Sub
End If

intFile = FreeFile

Open strPath For Output As #intFile

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryExport", dbOpenForwardOnly)

While Not rst.EOF
Print #intFile, Nz(rst!Tel, "")
rst.MoveNext
Wend

MsgBox "Export routine complete", _
vbInformation, "Export"

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Close #intFile

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[alessia]

Il Sun, 12 Oct 2003 20:15:58 +0000 (UTC), "Fletcher Arnold"
<fl****@home.com> ha scritto:

Hi Alessia
I am glad that you got the code to work. Don't worry about your English -
its fine, and very polite!
The following code is one possible version. It assumes your query is
"qryExport" which contains the field "Tel" - if not change the lines:
Set rst = dbs.OpenRecordset("qryExport" ...
and
Print #intFile, Nz(rst!Tel, "")

When you paste the new code in, make sure you compile the code -
Debug>Compile. If it does not compile, check Tools>References and make sure
the box Microsoft DAO 3.6 Object Library is checked.

Good Luck

Fletcher
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String
Dim intFile As Integer
Dim strLine As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strPath = InputBox("Enter file path", _
"Export", _
"C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

If Len(Dir(strPath)) > 0 Then
If MsgBox("This file already exists" & vbCrLf & _
"Would you like to overwrite it?", _
vbExclamation Or vbYesNoCancel, _
"Export") <> vbYes Then Exit Sub
End If

intFile = FreeFile

Open strPath For Output As #intFile

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryExport", dbOpenForwardOnly)

While Not rst.EOF
Print #intFile, Nz(rst!Tel, "")
rst.MoveNext
Wend

MsgBox "Export routine complete", _
vbInformation, "Export"

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Close #intFile

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Ciao Super - Fletcher
I mind giving you so much trouble, I have copied the code that you
have given me, I have modified the interested lines, I have compiled
the program...I press the button, I insert the path and the name of
the file, and this error signals me...

Error No: 3061
non enough parameters. 3
[I hope both corrected the translation]

the file test.txt has been created... but it is empty..

I have modified only these lines

Set rst = dbs.OpenRecordset("query_fuoriorario_cell",
dbOpenForwardOnly)
and
Print #intFile, Nz(rst!cell, "")
query_fuori_orario : it's my export query
cell : it's a field of query_fuori_orario
according to you, as can I resolve this error??

I don't so finally disturb still you...

thx, Fletcher....

where r u from Fletcher?

i live in venice...

have a nice day!

[Fletcher Arnold]

"alessia" <al*************@yahoo.it> wrote in message
news:0i********************************@4ax.com...

Il Sun, 12 Oct 2003 20:15:58 +0000 (UTC), "Fletcher Arnold"
<fl****@home.com> ha scritto:

Hi Alessia
I am glad that you got the code to work. Don't worry about your English -its fine, and very polite!
The following code is one possible version. It assumes your query is
"qryExport" which contains the field "Tel" - if not change the lines:
Set rst = dbs.OpenRecordset("qryExport" ...
and
Print #intFile, Nz(rst!Tel, "")

When you paste the new code in, make sure you compile the code -
Debug>Compile. If it does not compile, check Tools>References and make surethe box Microsoft DAO 3.6 Object Library is checked.

Good Luck

Fletcher
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim strPath As String
Dim intFile As Integer
Dim strLine As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strPath = InputBox("Enter file path", _
"Export", _
"C:\Shared\Test.txt")

If Len(strPath) = 0 Then Exit Sub

If Len(Dir(strPath)) > 0 Then
If MsgBox("This file already exists" & vbCrLf & _
"Would you like to overwrite it?", _
vbExclamation Or vbYesNoCancel, _
"Export") <> vbYes Then Exit Sub
End If

intFile = FreeFile

Open strPath For Output As #intFile

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryExport", dbOpenForwardOnly)

While Not rst.EOF
Print #intFile, Nz(rst!Tel, "")
rst.MoveNext
Wend

MsgBox "Export routine complete", _
vbInformation, "Export"

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Close #intFile

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Ciao Super - Fletcher
I mind giving you so much trouble, I have copied the code that you
have given me, I have modified the interested lines, I have compiled
the program...I press the button, I insert the path and the name of
the file, and this error signals me...

Error No: 3061
non enough parameters. 3
[I hope both corrected the translation]

the file test.txt has been created... but it is empty..

I have modified only these lines

Set rst = dbs.OpenRecordset("query_fuoriorario_cell",
dbOpenForwardOnly)
and
Print #intFile, Nz(rst!cell, "")
query_fuori_orario : it's my export query
cell : it's a field of query_fuori_orario
according to you, as can I resolve this error??

I don't so finally disturb still you...

thx, Fletcher....

where r u from Fletcher?

i live in venice...

have a nice day!

Error 3061 is caused by the query. I don't know what the SQL for
"query_fuoriorario_cell" looks like, but if you had:

SELECT TelNumber
FROM tblNumbers
WHERE SomeDate=[Forms]![frm1].[txtSomeDate]

Then the query would produce this error. It cannot work out this bit
[Forms]![frm1].[txtSomeDate].

The solution to this is to build up the sql string separately - but this can
be quite complicated. If you would like me to have a look at the database
for you then just create a copy, delete any private data and e-mail it to
me. (I''ve copied this message to your e-mail)

[alessia]

cut

Error 3061 is caused by the query. I don't know what the SQL for
"query_fuoriorario_cell" looks like, but if you had:

SELECT TelNumber
FROM tblNumbers
WHERE SomeDate=[Forms]![frm1].[txtSomeDate]

Then the query would produce this error. It cannot work out this bit
[Forms]![frm1].[txtSomeDate].

The solution to this is to build up the sql string separately - but this can
be quite complicated. If you would like me to have a look at the database
for you then just create a copy, delete any private data and e-mail it to
me. (I''ve copied this message to your e-mail)

thanks Fletcher!

I send you a copy of my database

send me your e-mail address in my private box

pa*****************@yahoo.it

you remove SPAM of the address

how can I reciprocate your kind help??

have a good day

[alessia]

Il Mon, 13 Oct 2003 15:38:15 GMT, alessia <al*************@yahoo.it>
ha scritto:

cut

Error 3061 is caused by the query. I don't know what the SQL for
"query_fuoriorario_cell" looks like, but if you had:

SELECT TelNumber
FROM tblNumbers
WHERE SomeDate=[Forms]![frm1].[txtSomeDate]

Then the query would produce this error. It cannot work out this bit
[Forms]![frm1].[txtSomeDate].

The solution to this is to build up the sql string separately - but this can
be quite complicated. If you would like me to have a look at the database
for you then just create a copy, delete any private data and e-mail it to
me. (I''ve copied this message to your e-mail)

thanks Fletcher!

I send you a copy of my database

send me your e-mail address in my private box

pa*****************@yahoo.it

you remove SPAM of the address

how can I reciprocate your kind help??

have a good day

opps....remove NOSPAM of the address...

pa*****************@yahoo.it
have a nice day!
ciao Fletcher!

[Fletcher Arnold]

"alessia" <al*************@yahoo.it> wrote in message
news:un********************************@4ax.com...

Il Mon, 13 Oct 2003 15:38:15 GMT, alessia <al*************@yahoo.it>
ha scritto:

cut

Error 3061 is caused by the query. I don't know what the SQL for
"query_fuoriorario_cell" looks like, but if you had:

SELECT TelNumber
FROM tblNumbers
WHERE SomeDate=[Forms]![frm1].[txtSomeDate]

Then the query would produce this error. It cannot work out this bit
[Forms]![frm1].[txtSomeDate].

The solution to this is to build up the sql string separately - but this canbe quite complicated. If you would like me to have a look at the databasefor you then just create a copy, delete any private data and e-mail it tome. (I''ve copied this message to your e-mail)

thanks Fletcher!

I send you a copy of my database

send me your e-mail address in my private box

pa*****************@yahoo.it

you remove SPAM of the address

how can I reciprocate your kind help??

have a good day

opps....remove NOSPAM of the address...

pa*****************@yahoo.it
have a nice day!
ciao Fletcher!

Sent e-mail
Hope to hear from you soon

Fletcher