Access Query Right Align

[Guest]

I am trying to export a query to a .txt file. I can't get the number field to
right align. Any help would be appreciated.

 

[MGFoster]

I am trying to export a query to a .txt file. I can't get the number field to
right align. Any help would be appreciated.

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

Numbers right-align automatically. You must be converting a number to a
string. Without seeing your query I can only guess at a solution.

If a string-number has to be right aligned you can convert it back to a
number like this:

CDbl(string_number)

This will convert the number to a Double. Use whatever number type
conversion you want [CDbl(), CInt(), CByte(), CCur(), etc.].

If that doesn't work you can use a padding expression on the string
number, but you have to know the width of the output column. Say the
width of the column was 7 characters. Something like the following
would right-align the string-number "123":

String(7-Len("123"),"0") & "123"

This will yield 0000123 in the output. (I used zeros to show the
padding that aligns the number to the right).

You can substitute the name of the string_number column for "123" (I'll
use a space as the padding this time):

String(7-Len(string_number), " ") & string_number

If the string_number value was "632" the output would look like this:

" 632"

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

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

iQA/AwUBRK1hgYechKqOuFEgEQKKTwCfTMq19PV1ZvwUARO07T73fbsQhLIAn1an
iYQpioAKtTHGNHXfw080AGM3
=AZ/K
-----END PGP SIGNATURE-----

 

[Guest]

Do you mind if I email you a pic of my query properties??

 

[Gary Walter]

I am trying to export a query to a .txt file. I can't get the number field
to
right align. Any help would be appreciated.

PMFBI

I just hate to see you waste time on something
that will frustrate you with marginal results...

if you choose Fixed Width in export text wizard,
all fields (number and text) will just plain left align..

you could try to change to a Tab delimited
with {none} Text Qualifier and play with
the fields as has been suggested

or

use VB to print out to the text file
and play with tabbing there also.

But..the best and simplest advice I can offer.....

Create a report based on your query
where all this can be done easily.

You can then export this report to a .txt or .rtf file.

If not quite what you want, go back and change
something in report where it is easy.

Aplogies again for butting in...

 

[Guest]

This export has to be in a specific format because it is going to be imported
into the accounts payable system. Is there no way to right align the number
field???

 

[Guest]

The export has specefic criteria like column width. This .txt file will be
imported into our A/P system, so the amount field has to be right aligned.

 

[John Vinson]

This export has to be in a specific format because it is going to be imported
into the accounts payable system. Is there no way to right align the number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Guest]

It still left-aligns the field. Here is what I put in the "field name" in the
design view of the query: amt: Format([true-amt],"000000.00")

 

[Guest]

It still left-aligns the field. Here is what I put in the "field name" in the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

This export has to be in a specific format because it is going to be imported
into the accounts payable system. Is there no way to right align the number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Gary Walter]

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

It still left-aligns the field. Here is what I put in the "field name" in
the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

This export has to be in a specific format because it is going to be
imported
into the accounts payable system. Is there no way to right align the
number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Guest]

Gary - here is the specs for the export specification. Everything I know
about Access I taught myself, so all that 'language' you posted may as well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I can save
in the folder where the export is to be placed and everythime I export, the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

It still left-aligns the field. Here is what I put in the "field name" in
the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

On Tue, 11 Jul 2006 09:54:01 -0700, Donna

This export has to be in a specific format because it is going to be
imported
into the accounts payable system. Is there no way to right align the
number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Gary Walter]

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

Gary - here is the specs for the export specification. Everything I know
about Access I taught myself, so all that 'language' you posted may as well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I can save
in the folder where the export is to be placed and everythime I export, the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

It still left-aligns the field. Here is what I put in the "field name" in
the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

:

On Tue, 11 Jul 2006 09:54:01 -0700, Donna

This export has to be in a specific format because it is going to be
imported
into the accounts payable system. Is there no way to right align the
number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Guest]

Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I really,
really appreciate your help.

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

Gary - here is the specs for the export specification. Everything I know
about Access I taught myself, so all that 'language' you posted may as well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I can save
in the folder where the export is to be placed and everythime I export, the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close






:
It still left-aligns the field. Here is what I put in the "field name" in
the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

:

On Tue, 11 Jul 2006 09:54:01 -0700, Donna

This export has to be in a specific format because it is going to be
imported
into the accounts payable system. Is there no way to right align the
number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Gary Walter]

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***

Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I really,
really appreciate your help.

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

Gary - here is the specs for the export specification. Everything I know
about Access I taught myself, so all that 'language' you posted may as well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I

can
save

in the folder where the export is to be placed and everythime I

export,
the

amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close






:
It still left-aligns the field. Here is what I put in the "field

name"
in

the
design view of the query: amt: Format([true-amt],"000000.00")

It looks great all thru the export after I apply the export specification,
but when the .txt file is created it is left aligned.

:

On Tue, 11 Jul 2006 09:54:01 -0700, Donna

This export has to be in a specific format because it is going to be
imported
into the accounts payable system. Is there no way to right align the
number
field???

You can use the Format() function to convert the number to a text
string. For example, an expression like

ExpNum: Format([numberfield], "00000000.00")

will convert the number to an 8 digit zero filled number with two
decimal places. If you need blank filled 8 digits, then

Right(" " & Format([numberfield], "#.00"), 8)

should do the job (though it will truncate larger numbers without
warning).

John W. Vinson[MVP]

 

[Guest]

Thanks for the code!!!! I know you are busy, so if you can't help me, please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I want the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***

Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I really,
really appreciate your help.

 

[Gary Walter]

this is all untested code...
plus, quick-and-dirty....

1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again

if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....

but I don't even know if what is
there works, or if it works, does
what you want.

2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"

if it is not there, then hit

<CTRL> G keys simultaneously

that's what they call the "Immediate Window"

3) if it works and does what you want

(all off top of my head, so probably
something wrong somewhere)

you could create a macro to run it,

or (recommended),
put a command button on a form
that runs it when clicked.

Thanks for the code!!!! I know you are busy, so if you can't help me,
please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I want the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***

Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I really,
really appreciate your help.


:

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

:
Gary - here is the specs for the export specification. Everything I know
about Access I taught myself, so all that 'language' you posted may
as
well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I can
save
in the folder where the export is to be placed and everythime I export,
the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"
text export subroutine for you.

We are real busy here preparing to convert
from ISBN's with 10 digits to new 13 digits,
or else one should just have a routine that
accepts

--SpecID for tblSaveIMEXColumns
(where one more field was added to it say
"RightAlign" Yes/No, default 0)

--recordset of data

--path/filename of text file

It's not hard to print a string you prepare
yourself to a text file.

Dim hFile As Long
Dim strPath As String
Dim strTextLine As String
Dim rst As DAO.Recordset

strPath = "C:\xxx.txt"

strSQL = "SELECT ...."
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field



'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

 

[Guest]

I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt

this is all untested code...
plus, quick-and-dirty....

1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again

if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....

but I don't even know if what is
there works, or if it works, does
what you want.

2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"

if it is not there, then hit

<CTRL> G keys simultaneously

that's what they call the "Immediate Window"

3) if it works and does what you want

(all off top of my head, so probably
something wrong somewhere)

you could create a macro to run it,

or (recommended),
put a command button on a form
that runs it when clicked.

Thanks for the code!!!! I know you are busy, so if you can't help me,
please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I want the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***


Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I
really,
really appreciate your help.


:

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

:
Gary - here is the specs for the export specification. Everything I
know
about Access I taught myself, so all that 'language' you posted may
as
well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file which I
can
save
in the folder where the export is to be placed and everythime I
export,
the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if this
matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"

 

[Gary Walter]

did you cut the attempt earlier
to run the function in the wrong
window?

I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt

this is all untested code...
plus, quick-and-dirty....

1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again

if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....

but I don't even know if what is
there works, or if it works, does
what you want.

2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"

if it is not there, then hit

<CTRL> G keys simultaneously

that's what they call the "Immediate Window"

3) if it works and does what you want

(all off top of my head, so probably
something wrong somewhere)

you could create a macro to run it,

or (recommended),
put a command button on a form
that runs it when clicked.

Thanks for the code!!!! I know you are busy, so if you can't help me,
please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I want
the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the
page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

:

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***


Gary - The DataType is text for all fields. The field that needs to
be
right-aligned is the "amt" field. I know this is asking a lot, but I
really,
really appreciate your help.


:

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

:
Gary - here is the specs for the export specification.
Everything I
know
about Access I taught myself, so all that 'language' you posted
may
as
well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file
which I
can
save
in the folder where the export is to be placed and everythime I
export,
the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if
this
matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

If you copy specs for your export
from tblSaveIMEXColumns here
in readable form
(actually only need following fields):

DataType FieldName Start Width

and identify field you want right-aligned

I will try to pump out a "quick-and-dirty"

 

[Guest]

I deleted the original modTest and created a new one. When I ran it, this is
the message I received:

3265 Item not found in this collection.

did you cut the attempt earlier
to run the function in the wrong
window?

I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt

this is all untested code...
plus, quick-and-dirty....

1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again

if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....

but I don't even know if what is
there works, or if it works, does
what you want.

2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"

if it is not there, then hit

<CTRL> G keys simultaneously

that's what they call the "Immediate Window"

3) if it works and does what you want

(all off top of my head, so probably
something wrong somewhere)

you could create a macro to run it,

or (recommended),
put a command button on a form
that runs it when clicked.

:
Thanks for the code!!!! I know you are busy, so if you can't help me,
please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I want
the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the
page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

:

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***


Gary - The DataType is text for all fields. The field that needs to
be
right-aligned is the "amt" field. I know this is asking a lot, but I
really,
really appreciate your help.


:

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

:
Gary - here is the specs for the export specification.
Everything I
know
about Access I taught myself, so all that 'language' you posted
may
as
well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file
which I
can
save
in the folder where the export is to be placed and everythime I
export,
the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if
this
matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,

 

[Gary Walter]

I never use spaces in query/table names,
so forgive me if I forget to bracket..

change following code line
(put brackets around query name)

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

to

Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _
dbOpenSnapshot)

that is...if query name is for sure "AP Payment Export"

save your code and compile again

if you put a question mark in front of the
function when you type in Immediate
Window, you will get "True" if it completes
properly, i.e., in Immediate Window, type

?fExpAP_Pmt

hopefully, you will get True w/change

I deleted the original modTest and created a new one. When I ran it, this
is
the message I received:

3265 Item not found in this collection.

did you cut the attempt earlier
to run the function in the wrong
window?

I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt


:

this is all untested code...
plus, quick-and-dirty....

1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again

if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....

but I don't even know if what is
there works, or if it works, does
what you want.

2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"

if it is not there, then hit

<CTRL> G keys simultaneously

that's what they call the "Immediate Window"

3) if it works and does what you want

(all off top of my head, so probably
something wrong somewhere)

you could create a macro to run it,

or (recommended),
put a command button on a form
that runs it when clicked.

:
Thanks for the code!!!! I know you are busy, so if you can't help
me,
please
let me know. I absolutely do not want to hinder you.

I have a couple of questions:

1. "C:\xxx.txt" - is this where I input the location of where I
want
the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the
page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?

:

start a new module

copy and paste this function
into the new module

save module as "modTest"

in top menu, click on Compile..
from "Debug" drop-down

if all above went well,
test it by:

in Immediate Window at bottom
of window, type

fExpAP_Pmt

and hit <ENTER> key


'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant

strPath = "C:\xxx.txt"

Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)

With rst
.MoveFirst

'First get a new file handle
hFile = FreeFile


'Open the strPath to write the text
Open strPath For Output As hFile

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field

'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld

'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld

'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld

'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld

'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld

'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld

'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld

'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld

'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld

'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rst.Close

fExpAP_Pmt = True

Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function

Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***


Gary - The DataType is text for all fields. The field that needs
to
be
right-aligned is the "amt" field. I know this is asking a lot,
but I
really,
really appreciate your help.


:

missing 1 requested field please

DataType FieldName Start Width

this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....

if that's too complicated for you,
tell me now so I don't waste my
time please...

if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"

thanks

:
Gary - here is the specs for the export specification.
Everything I
know
about Access I taught myself, so all that 'language' you
posted
may
as
well
be Greek. haha I appreciate your help.

If I understand correctly, you can create a schema.ini file
which I
can
save
in the folder where the export is to be placed and everythime
I
export,
the
amount field will right align, right??

The name of the Query I am exporting is AP Payment Export if
this
matters.

Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3
amt 46 9
FILLER1 55 1
case# 56 25
charge 81 4
FILLER5A 85 5
FREQ 90 2
FILLER10 92 10

:

Did you save as a "spec?"

Then one of the "SpecID's" in
the table created by following SQL
will be what you have so far....
which I understand you as saying
works great except for alignment
of one field...

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,