Leading zeros

I am developing a database with 135 fields, many of which must output right-justified with leading zeros with the sum of the characters entered and the zeros equaling the field length. The database output will be a fixed length string of 860 characters - the sum of all the field lengths. Fields that are blank must contain spaces equal to the field length.

I cannot find a solution in Access help (I am using Access 2000) or on-line. Any help with this will be greatly appreciated.

"=?Utf-8?B?THlubmU=?=" .(donotspam) wrote in
:


I am developing a database with 135 fields,

.... databases are usually made up of tables rather than fields. Tables are
made up of fields, but 135 fields in one table is _diagnostic_ of a Major
Design Problem. You really will be well served by applying some
normalisation to this setup.

many of which must output
right-justified with leading zeros with the sum of the characters
entered and the zeros equaling the field length.

That is not hard: you can use the Format property of the text box on the
form or report, and set to something like "00000000" for eight digits.

The database output
will be a fixed length string of 860 characters - the sum of all the
field lengths. Fields that are blank must contain spaces equal to the
field length.

For really tight output specifications, you are probably best served by
looking at the TransferText method, or even using Print # commands and a
specific VBA routine.

But before anything else, you really need to fix the table design.

Hope that helps


Tim F

To create fixed format output you need to experiment a little. I have the easiest time when I create a query that orders the fields as they need to be exported and formats the dates and numerica fields as the target app is expecting them. Then you need to create an export specification. To do that you must export the query once (or as many times as it takes to get it right) manually using the File/save as wizard. It will give you the choice of delimited or fixed. You will need to press the advanced tab to see all the options and to adjust the lengths of some of the fields if necessary. This is also where you'll see the option to save the spec.

Once you have exported the file in the correct format, you can then use either the TransferText Method or Action and reference the export spec by name. All the settings will be applied and in the future it will take only a single instruction to export the file.

You could write a lengthy VBA procedure to format the output but you probably don't need to. If you haven't used the export wizard before you might not get it right the first time. But learn from each export and you'll be amazed at how easy it really is.
--
Bridge Players know all the Tricks


"Lynne" wrote:

I am developing a database with 135 fields, many of which must output right-justified with leading zeros with the sum of the characters entered and the zeros equaling the field length. The database output will be a fixed length string of 860 characters - the sum of all the field lengths. Fields that are blank must contain spaces equal to the field length.

I cannot find a solution in Access help (I am using Access 2000) or on-line. Any help with this will be greatly appreciated.