Concatenating fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Reggiee - 30 Mar 2006 11:10 GMT

Hi,

I need to concatenate 3 fields where all the fields are number format.

The problem is this - when concatenating them I lose any leading 0's that
may exist in the original tables even thought ive formatted them as for
example 000 to make access put in the leading 0's.

How can I write some SQL which makes the answer more like 001-01-100 rather
than 1-1-100.

Hope this makes sense

Thanks

Reggiee

Reply to this Message

John Spencer - 30 Mar 2006 13:39 GMT

Numbers are stored without leading zeroes (or trialing zeroes). To do what
you want you are going to have to use the Format function

For your particular example.1-1-100 to 001-01-100, try

Format([Field1],"000\-") & Format([Field2],"00\-") & Format([Field3],"000")

I include the dash (-) in the formatting, but you can simply concatenate
that in as a separate item.
Format([Field1],"000") & "-" & Format([Field2],"00") & "-" &
Format([Field3],"000")

> Hi,
>
[quoted text clipped - 13 lines]
>
> Reggiee

Reply to this Message

Graham R Seach - 30 Mar 2006 13:47 GMT

Reggie,

Format(Field1, "000-") & Format(Field1, "00-") & Format(Field1, "000")

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

> Hi,
>
[quoted text clipped - 13 lines]
>
> Reggiee

Reply to this Message

Reggiee - 03 Apr 2006 08:53 GMT

Thanks both

Easy when you know how eh ??

Reggiee

> Reggie,
>
[quoted text clipped - 23 lines]
> >
> > Reggiee

Reply to this Message