How do i keep the leading zeros in text

[nerosh]

hi, i have a field that concatenates a prefix and an auto number, e.g, Field1= 001, Field2=project. in field 3, my formula reads, left(field1;3) & field2. the result is PR1, i need it to be PR001. the leading zeros in filed1 seems to drop in field3. how do i prevent this?

[PhilOfWalton]

Try

Field2 & Right(Format(1,"0000000"),3)
 

Format (Field1, "0000000") ensures that there are lots of leading zeros, and then we cut it to the right hand 3 digits.

Simpler would be

Field2 & Format(1,"000")
 

The possible snag with this is it is fixed at 3 digits. The first method allows you to pass a parameter od how many digits to display.

Phil

[nerosh]

Hi Phil, thank you for your speedy response. my formula now reads: Left([Field1];3) & "-" & [Field2] & Right(Format(1,"0000000"),3), however i get an error that the expression contains invalid syntax. I've tried both methods and get the same error. apologies if this is a dumb question - learning as i go along

[PhilOfWalton]

Sorry, my fault.

Your question is a bit confusing.
If Field2 = "PR" and Field1 = 1 then the correct (I hope) expression should be

Field2 & Right(Format(Field1,"0000000"),3)
 

The invalid syntax you mentioned, apart from anything else, is that you have a semicolon in Left([Field1];3) instead of a comma.

You can of course add the dash if you want it

Field2 & "-" & Right(Format(Field1,"0000000"),3)
 

Phil