Leading zeros 2

[Mattb7]

ok, i am building an access data base at work, the field is required to be long integer. I need to then export the data to a non delimited text file. I have all the specs as to what the column width needs to be, but a field size needs to be something like 12, but only requires entering a 4 digit number. Therefore they want it to export as: 000000001234. How do I make a long integer filed have leading xeros like this???????

 

[SgtJarrow]

In the table, or form, or wherever, set the Format property of the field/control to have twelve zeros...000000000000 Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[GingerR]

you can write a query which then you'll export your data from. this way you can get your data from what it is in the db to what it needs to be for the txt file.

for this one case, use cstr (convert to string), len() and string() to add leading zeros.

Here I am taking a field in my db called "SaleOrderID"; adding enuf leading zeros to make it 12 characters long; then naming the resulting field "Output":

Output: CStr(String(12-Len([SaleOrderID]),"0&quot & [SaleOrderID])

 

[jennilein]

GingerR -

I just posted a similar question...the only difference is that I need to export to fixed width that contains a decimal point and two spaces to the right of the decimal point.

i.e. 25.10 needs to be 12 in length like 000000025.10

How can I get this?

 

[TonyJollans]

Hi jennilein,

IMHO the solution offered above is over complex. The Format Function outputs a string, so ..

Format(YourField,"000000000.00&quot

.. should do what you ask.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[jennilein]

Tony-

You're my hero...been struggling with this for the past hour!!! Who would have know it could be that easy!

Thanks! (it worked, in case you couldn't tell)
Jennifer

 

[TonyJollans]

My pleasure!

Thanks for the star.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[jennilein]

Okay, the last tip really worked for me, however, I have a new text format for prices that have to be created. They should change a number $13.5 to a text string of 7 charachters - no decimal spaces - "0001350".

I tried the format function in access with "0000000" but it's actually rounding off my numbers to "0000014".

Any suggestions??

 

[Golom]

Using a variation from Tony
[blue][tt]
Format(YourField * 100,"000000000")
[/tt][/blue]

 

[jennilein]

THANKS! Worked like a charm!

 

[airmojo]

I have exported fixed length files before from Access which I have uploaded to the mainframe. Numeric fields appeared right-justified and no leading zeros, so I wrote mainframe program code to parse the data into a proper mainframe-numeric field before actually using the 'numeric' data.

I was curious as to how to export with leading-zeros today, so I decided to play around a bit.

I find it strange that the 'Format' property does not sufficiently tell Access how to export the data. Especially cumbersome when the query you are exporting data from is using 'group-by' and 'Summed' numerics. In this case, I had to create a subsequent query to use the 'Format' function for each numeric field, where you have to assign it to another calcualated field or expression.

This all could be even easier if the 'Format' property worked as it should...even for exported fields!

Just my 2 cents worth... maybe I'm still confused...

AirMojo