Re: Access Query Right Align

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" wrote:

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

"Donna" wrote:
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

"Gary Walter" wrote:

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






"Donna" wrote:
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.

"John Vinson" wrote:

On Tue, 11 Jul 2006 09:54:01 -0700, Donna
<Donna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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]







.

Relevant Pages

  • Re: Access Query Right Align
    ... Dim hFile As Long ... Dim strPath As String ... Dim strTextLine As String ...
    (microsoft.public.access.queries)
  • Re: Access Query Right Align
    ... Gary - here is the specs for the export specification. ... Dim hFile As Long ... Dim strPath As String ... Dim strTextLine As String ...
    (microsoft.public.access.queries)
  • Re: Access Query Right Align
    ... Dim hFile As Long ... Dim strPath As String ... Dim strTextLine As String ...
    (microsoft.public.access.queries)
  • Help!! Oracle 9.2.0.5.0 wont properly read the *.rsp
    ... #Datatype: String ... #Datatype: Boolean ... #This page shows the current status in the installation. ...
    (comp.databases.oracle.server)
  • Re: Order in a array / ListBox
    ... End Sub ... Optional strInputFile As String, _ ... Dim LB1 As Long ... Dim hFile As Long ...
    (microsoft.public.excel.programming)