New Access User!! - Export Query Building - How To??

[k3639]

1) I am trying to convert an access table into a fixed length text file.
2) The problem is, I cannot convert an access numeric field into a fixed
length, right justified, zero filled, numeric field. In the case of a
numeric field with 2 defined decimal places, I am unable to tell the
conversion process to drop the decimal indicator while retaining to
characters to the right of the decimal point.

I was told I need to build a query and export from the query, but have no
idea where to go with it. Please help!!

Thanks!

 

[Pat Hartman\(MVP\)]

Select ..., Format(CLng(YourField * 100),"000000000") As Exp1, ....
You didn't specify how many fixed positions you needed so I choose 9. Add
or delete 0's to change the final text length of the field. Working from the
inside out -
Multiply yourfield by 100 to shift decimal values to the left.
Convert the result to a long integer to remove the decimal places.
Format the long integer as a 9 character text field with leading zeros.

 

[Ken Sheridan]

I a query use the following expression as a computed column to return the
number as a fixed length string, in this example 6 characters in length, with
leading zeros where necessary:

Format(Int([MyNumber]) & Right(Format([MyNumber],"#.00"),2),"000000")

where MyNumber is the name of the floating point number column in the table.
Just enter the above in the 'field' row of a blank column in the query
design grid and precede it with the name you want to give the column followed
by a colon, e.g.

MyNumber:Format(Int etc.

To export this as a a fixed width text file select the query in the database
window and then select File|Export on the main menu bar. In the common
dialogue navigate to the destination folder, select text files from the file
types list and enter the name you wish to save it as. Click the export
button which will start the export wizard. Then just follow the instructions.

Ken Sheridan

 

[k3639]

Thanks for the assistance Pat. What I have is a Mainframe export, where all
field names begin with R007- and the first would be a 5 digit field, next, a
9 digit field, then a 2 digit field, etc. How do I specify the "Your Field"
entries, and do I need to include all table fields in my query, even text
fields? Mixed in with my numeric fields are several text fields like user
name, address, telephone, city, state, zip, sex, etc. I have been given
suggestions from writing code, to many different query commands, and have
been told that for example: my first field name is R007-CHAPTER NUMBER, that
I cannot use that full name to title my query, but must create a similar
reference, like Select CHAPNUM, Format(CLng(R007-CHAPTER NUMBER *
100),"000000000"), but when I try this, I get an error message referring to
an invalid function. I was also told I could choose a Fixed Length Data
Export and pad the fields properly, which appeared to be working, until I
displayed the final results, and fields were all merged together incorrectly.
As mentioned, I am new to this, please pardon the inexperience!

 

[Pat Hartman\(MVP\)]

1. Create a query using the QBE that selects all the columns you want to
export in the order they should appear in the exported file.
2. If your column names contain embedded spaces and/or special characters
(both of which are poor practice), you need to enclose the name in square
brackets - [R007-CHAPTER NUMBER] - so that Access can identify the start and
end of the name.
3. For the column you need to reformat, place the following in the Field
cell.
ChapNum7:Format(CLng([R007-CHAPTER NUMBER] * 100),"000000000")