Concatenating fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
Back
Top