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

Combining 2 fields into another 3rd field and leading zeros

P: 11
Hi, I'm trying to combine the text in two original fields and paste them into another 3rd field on a form, but the problem is that there are leading zeros in both original fields, which access does not copy into the 3rd field.

For example, if field 1 = 07 and field 2 = 0001, i'd like the 3rd field to be 070001, but instead access returns 71 in the 3rd field.

Adding to the complexity a bit is that field 2 is an autonumber field. so when i want that autonumber field to have a value when creating a new record, i have to fill in at least one of the other fields on the form. I'll be using field 4 for this.

The code I'm currently using is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Field4_Exit(Cancel As Integer)
  2. Me.Field3= Me.Field1 & Me.Autonumber
  3. End Sub
How can i modify this code so that the leading zeros are included?

Thanks a lot!
Mar 14 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Hi, I'm trying to combine the text in two original fields and paste them into another 3rd field on a form, but the problem is that there are leading zeros in both original fields, which access does not copy into the 3rd field.

For example, if field 1 = 07 and field 2 = 0001, i'd like the 3rd field to be 070001, but instead access returns 71 in the 3rd field.

Adding to the complexity a bit is that field 2 is an autonumber field. so when i want that autonumber field to have a value when creating a new record, i have to fill in at least one of the other fields on the form. I'll be using field 4 for this.

The code I'm currently using is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Field4_Exit(Cancel As Integer)
  2. Me.Field3= Me.Field1 & Me.Autonumber
  3. End Sub
How can i modify this code so that the leading zeros are included?

Thanks a lot!
Expand|Select|Wrap|Line Numbers
  1. Private Sub Field4_Exit(Cancel As Integer)
  2. Me.Field3 = Right("0" & Me.Field1, 2) & Right("000" & Me.Field2, 4)
  3. End Sub
Mar 14 '07 #2

P: 11
Thanks! this works great
Mar 15 '07 #3

Rabbit
Expert Mod 10K+
P: 12,359
No problem, good luck.
Mar 15 '07 #4

Post your reply

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