Concatenating fields

[Guest]

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

 

[John Spencer]

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")

 

[Graham R Seach]

Reggie,

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

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Guest]

Thanks both

Easy when you know how eh ??

Reggiee