Preceding Zeros with Concatenation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lilbomshl - 16 Dec 2004 05:05 GMT

I have 3 different fields in my table, one is a 2 digit number field, one is
a 3 digit autonumber that starts at 001, and the last is a 2 character text
field. I need to concatenate these along with some special characters
example: 04-001-AH to show up on my form. My problem is I can't get the
autonumber field to show the preceding zeros in the concatenation. I
formatted it on it's own in my table as 000 but with the concatenation it
just goes back to not showing the preceding zeros.

Reply to this Message

Duane Hookom - 16 Dec 2004 05:25 GMT

Use the format() function
& Format([YourField],"000")

Signature

Duane Hookom
MS Access MVP

> I have 3 different fields in my table, one is a 2 digit number field, one is
> a 3 digit autonumber that starts at 001, and the last is a 2 character text
[quoted text clipped - 3 lines]
> formatted it on it's own in my table as 000 but with the concatenation it
> just goes back to not showing the preceding zeros.

Reply to this Message

lilbomshl - 20 Dec 2004 04:07 GMT

Thank you Duane!!!

> Use the format() function
> & Format([YourField],"000")
[quoted text clipped - 9 lines]
> > formatted it on it's own in my table as 000 but with the concatenation it
> > just goes back to not showing the preceding zeros.

Reply to this Message

Allen Browne - 16 Dec 2004 05:27 GMT

Use the Format function around the number field.

The Control Source of the text box will be something like this:
=[Field1] & Format([Field2], "\-000\-") & [Field3]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have 3 different fields in my table, one is a 2 digit number field, one
>is
[quoted text clipped - 6 lines]
> formatted it on it's own in my table as 000 but with the concatenation it
> just goes back to not showing the preceding zeros.

Reply to this Message

lilbomshl - 20 Dec 2004 04:07 GMT

Thank you Allen!!!

> Use the Format function around the number field.
>
[quoted text clipped - 11 lines]
> > formatted it on it's own in my table as 000 but with the concatenation it
> > just goes back to not showing the preceding zeros.

Reply to this Message