Converting Access Data to an Ascii text file

[Guest]

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

 

[MGFoster]

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[Guest]

Thank you,
Yes, the format feature does work and then I used export, selected Asc
format and I am able to create the ASCII file. I already sent it to the
payroll company and they are happy.
I now just have one more problem. I am can follow VBA but certainly not
write it. Is there anyone who can help me automate the export process to that
a user can just hit a button and create it?
I Query is called QrySoutheastExportFinal, which has only one field called
CVSFile and it is the field, where I combined all field required by the
payroll company.
I manually have been saving it with PCI1.asc, as a text delimer I am using
comma, because there are none and the text qualifier is none.
This creates a perfect ascii file.
Anne

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[Guest]

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[MGFoster]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

:

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You'll need an export specification. Here's how to get one: Export
the query to a junk file (something you'll delete after all this). When
the Export Wizard dialog box comes up click the Advanced button. Set up
the export to the way you want and save the Export specification.
Remember the name of the specification.

2. Create a Command Button named cmdExport. Use the following as its
OnClick event.

Private Sub cmdExport_Click()

Const QRY = "QrySoutheastExportFinal"
Const SPEC = "SoutheastExportSpec"
Const FILE = "C:\Temp\SE_Export.txt"

On Error GoTo err_

DoCmd.TransferText acExportDelim, SPEC, QRY, FILE

exit_:
Exit Sub

err_:
MsgBox "An Error occurred while exporting the data" & _
vbcr & vbcr & "Error: " & err.Description, vbExclamation
Resume exit_

End Sub

Substitute the real names for the SPEC and FILE.

Read the Access VBA help on the TransferText method (in the VBA
immediate window [Ctrl-G], type TransferText. Put the cursor on the
word and hit the F1 key, the Help article will appear).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJ2d6YechKqOuFEgEQKlDwCdGK2vZe8rRYiXr1PeLIgwJ662TzIAn0lW
IAsojeMEyCovWSh6f0JHtXXB
=Rtyk
-----END PGP SIGNATURE-----

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

:

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[Guest]

Very Nice,
Thank you,
Anne

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You'll need an export specification. Here's how to get one: Export
the query to a junk file (something you'll delete after all this). When
the Export Wizard dialog box comes up click the Advanced button. Set up
the export to the way you want and save the Export specification.
Remember the name of the specification.

2. Create a Command Button named cmdExport. Use the following as its
OnClick event.

Private Sub cmdExport_Click()

Const QRY = "QrySoutheastExportFinal"
Const SPEC = "SoutheastExportSpec"
Const FILE = "C:\Temp\SE_Export.txt"

On Error GoTo err_

DoCmd.TransferText acExportDelim, SPEC, QRY, FILE

exit_:
Exit Sub

err_:
MsgBox "An Error occurred while exporting the data" & _
vbcr & vbcr & "Error: " & err.Description, vbExclamation
Resume exit_

End Sub

Substitute the real names for the SPEC and FILE.

Read the Access VBA help on the TransferText method (in the VBA
immediate window [Ctrl-G], type TransferText. Put the cursor on the
word and hit the F1 key, the Help article will appear).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJ2d6YechKqOuFEgEQKlDwCdGK2vZe8rRYiXr1PeLIgwJ662TzIAn0lW
IAsojeMEyCovWSh6f0JHtXXB
=Rtyk
-----END PGP SIGNATURE-----

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

:

Anne wrote:

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[Guest]

This works just perfect.
What if I wanted to add today's date to the Se_Export.txt?
Anne

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You'll need an export specification. Here's how to get one: Export
the query to a junk file (something you'll delete after all this). When
the Export Wizard dialog box comes up click the Advanced button. Set up
the export to the way you want and save the Export specification.
Remember the name of the specification.

2. Create a Command Button named cmdExport. Use the following as its
OnClick event.

Private Sub cmdExport_Click()

Const QRY = "QrySoutheastExportFinal"
Const SPEC = "SoutheastExportSpec"
Const FILE = "C:\Temp\SE_Export.txt"

On Error GoTo err_

DoCmd.TransferText acExportDelim, SPEC, QRY, FILE

exit_:
Exit Sub

err_:
MsgBox "An Error occurred while exporting the data" & _
vbcr & vbcr & "Error: " & err.Description, vbExclamation
Resume exit_

End Sub

Substitute the real names for the SPEC and FILE.

Read the Access VBA help on the TransferText method (in the VBA
immediate window [Ctrl-G], type TransferText. Put the cursor on the
word and hit the F1 key, the Help article will appear).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJ2d6YechKqOuFEgEQKlDwCdGK2vZe8rRYiXr1PeLIgwJ662TzIAn0lW
IAsojeMEyCovWSh6f0JHtXXB
=Rtyk
-----END PGP SIGNATURE-----

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

:

Anne wrote:

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----

 

[Guest]

I got it to work with the date with a slight variation;
Private Sub cmdExportfile_Click()
Dim sToday As String
sToday = Format(Date, "mmddyy")
Const QRY = "QrySoutheastExportFinal"
Const SPEC = "SoutheastExportSpec"

DoCmd.TransferText acExportDelim, SPEC, QRY, "C:\Access\PCI_Export" & sToday
& ".txt"
MsgBox "The file has been created! Please look for C:\Access\PCI_Export with
Today's date"

End Sub

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You'll need an export specification. Here's how to get one: Export
the query to a junk file (something you'll delete after all this). When
the Export Wizard dialog box comes up click the Advanced button. Set up
the export to the way you want and save the Export specification.
Remember the name of the specification.

2. Create a Command Button named cmdExport. Use the following as its
OnClick event.

Private Sub cmdExport_Click()

Const QRY = "QrySoutheastExportFinal"
Const SPEC = "SoutheastExportSpec"
Const FILE = "C:\Temp\SE_Export.txt"

On Error GoTo err_

DoCmd.TransferText acExportDelim, SPEC, QRY, FILE

exit_:
Exit Sub

err_:
MsgBox "An Error occurred while exporting the data" & _
vbcr & vbcr & "Error: " & err.Description, vbExclamation
Resume exit_

End Sub

Substitute the real names for the SPEC and FILE.

Read the Access VBA help on the TransferText method (in the VBA
immediate window [Ctrl-G], type TransferText. Put the cursor on the
word and hit the F1 key, the Help article will appear).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJ2d6YechKqOuFEgEQKlDwCdGK2vZe8rRYiXr1PeLIgwJ662TzIAn0lW
IAsojeMEyCovWSh6f0JHtXXB
=Rtyk
-----END PGP SIGNATURE-----

Acutally, I did contcatenate the query's columns into one column and
apparently it works just fine, because the payroll company confirmed that it
is perfect.
Now my problem is automating the export process of this once field, which I
can do fine manually, but I dont know how to write the code for it to be done
from a button.
Anne

:

Anne wrote:

A clients is using my database to enter time for payroll. The payroll company
has requested an Ascii text file to transmit the data. I started doing this
using [field1]&""&[field2] etc., which is ok, until they want a lot of zeros.
I am running into a problem, where they want for example the hours worked
field, 10.00 Hrs to be formatted as 000010.00
Is it possible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. Use the Format() function on the query's column values. E.g.:

SELECT Format(sales_amt,"000000.00"), ... etc.
FROM ...

If sales_amt = 5.3344, the output would be 000005.33.

Don't contcatenate the query's columns, just let the Export process
format the output in comma-delimited or tab-delimited format. See the
Access Help article "TransferText method" for more info.

Also, right click on the query & select Export. Select Text Files as
the output type. Follow the wizard's directions to finish.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJxACIechKqOuFEgEQLKQACeL05qXx8bux+038qXPjdlm0bjYkYAn1+M
DR6jvtZRmxkvE863o5g+qyJ5
=7rcw
-----END PGP SIGNATURE-----