By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

How do i keep the leading zeros in text

P: 2
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?
Nov 14 '16 #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
Try

Expand|Select|Wrap|Line Numbers
  1. Field2 & Right(Format(1,"0000000"),3)
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. Field2 & Format(1,"000")
  2.  
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
Nov 14 '16 #2

P: 2
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
Nov 15 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
Sorry, my fault.

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

Expand|Select|Wrap|Line Numbers
  1. Field2 & Right(Format(Field1,"0000000"),3)
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. Field2 & "-" & Right(Format(Field1,"0000000"),3)
  2.  
Phil
Nov 15 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.