Leading Zeros New Post

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I need to add leading zeroes to an amount field in a
query. Some of the amounts are longer than others. The
field length needs to be 7 characters.

With some fields longer than others how do I get the query
to determine how many zeros to add?

Also this is eventually going to be converted to a text
file for programming purposes. Is there an easy way to get
rid of the decimal point in the amount field as well?

Thanks for your help :o)

-Chris
 
Create a query into this table.
Type this in to the Field row:
NewName: Format([SomeField], "0000000")

The expression will presnt the values with leading zeros, and with any
decimals rounded off.

Use TransferText to export the query to a text file with the leading zeros.
 
Great thanks for the info. On the second part though I
can't round the decimals I need to actually keep the
numbers after the decimals but just get rid of the
decimal. Any suggestions?

-Chris
-----Original Message-----
Create a query into this table.
Type this in to the Field row:
NewName: Format([SomeField], "0000000")

The expression will presnt the values with leading zeros, and with any
decimals rounded off.

Use TransferText to export the query to a text file with the leading zeros.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Chris said:
I need to add leading zeroes to an amount field in a
query. Some of the amounts are longer than others. The
field length needs to be 7 characters.

With some fields longer than others how do I get the query
to determine how many zeros to add?

Also this is eventually going to be converted to a text
file for programming purposes. Is there an easy way to get
rid of the decimal point in the amount field as well?

Thanks for your help :o)

-Chris
Click to expand...


.
Click to expand...
 
It can be done and the complexity of the solution depends on the complexity of
the data. If the data is all to be rounded to dollars from dollars and cents
then just multiply by 100 and then format as Allen pointed out.

NewName: Format([Somefield]*100,"0000000")

If the data is more complex then that you need to post back and let us know.
Great thanks for the info. On the second part though I
can't round the decimals I need to actually keep the
numbers after the decimals but just get rid of the
decimal. Any suggestions?

-Chris
-----Original Message-----
Create a query into this table.
Type this in to the Field row:
NewName: Format([SomeField], "0000000")

The expression will presnt the values with leading zeros, and with any
decimals rounded off.

Use TransferText to export the query to a text file with the leading zeros.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Chris said:
I need to add leading zeroes to an amount field in a
query. Some of the amounts are longer than others. The
field length needs to be 7 characters.

With some fields longer than others how do I get the query
to determine how many zeros to add?

Also this is eventually going to be converted to a text
file for programming purposes. Is there an easy way to get
rid of the decimal point in the amount field as well?

Thanks for your help :o)

-Chris
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top