leading 'Zeros' in data

[wasim_sono]

Dear All

I have a table having different text field( about 12 fields). The data has been filled through a form. What I need is that export this data in text file for COBOL processing. But it requires fixed width of fields.

For example I entered the following data:



widht3 width10 width5
------- --------- -------
2 452 96

but it should store in table like

002 0000000452 00096

I hope it is helpful for understanding.

Thanks in advance.


Wasim

[matthewspatrick]

Wasim,

You could use the Format function to force leading zeroes:

SELECT Format([Field1], "000"), Format([Field2], "0000000000"), Format([Field3], "00000")
FROM YourTable

[FREEEEEEDOM]

I am having a similar problem keeping those zeroes...

You can change the format like Wasim suggested, which will change the display of your numbers. However, the data isn't stored like that (ie- "001" is still "1"). So when you import that data into COBOLT the zeroes will drop again.

Currently, this is my round-about solution until I find another one:

• Change the format to the width you need in Design View, as Wasim suggested

Once the format looks good on your Access Table:

• Copy the table into Excel
• Copy the table into Notepad and save as .txt file
• Re-import the file into Access, changing the data types from Long Integer to Text in the import wizard

That will create a Text field for your record instead of numerical values.

Unfortunately, simply changing the data type from Number to Text once you changed the format won't hold the leading 0's...

Hope that helps

[RuralGuy]

Dear All

I have a table having different text field( about 12 fields). The data has been filled through a form. What I need is that export this data in text file for COBOL processing. But it requires fixed width of fields.

For example I entered the following data:

widht3 width10 width5
------- --------- -------
2 452 96

but it should store in table like
002 0000000452 00096
I hope it is helpful for understanding.
Thanks in advance.

Wasim

If you want the text fields in the table to have the leading zeros then I would suggest you use the Format() function in the AfterUpdate event of each control to adjust the field as the values are being entered.