Need to a text field a value made up of 3 letters + 00000000 +01 through 999999 and have the total length to be no more than 20 long
Need to a text field a value made up of 3 letters + 00000000 +01 through 999999 and have the total length to be no more than 20 long
ASKER
I am looking for a way to to put a string together to place into a text field.
Field name is Vendor_Number
I have an autonumber field called VendorID
and I need the Vendor_Number to look like this - VEN0000001
This value represents the number FIRST auto number with the VEN0000001 - 10 zeros in between the VEN0000000001
Then from that point on the right side would end with the VendorID number but be right justified so the positions and zeros will always have no more thank 10 spaces so the first number is VEN0000000001 and 999 would be VEN0000000999 if the number grows to VEN0000099999 then the number of zeros goes down but the number of spaces and numbers after the VEN are the 10 spaces.
Field name is Vendor_Number
I have an autonumber field called VendorID
and I need the Vendor_Number to look like this - VEN0000001
This value represents the number FIRST auto number with the VEN0000001 - 10 zeros in between the VEN0000000001
Then from that point on the right side would end with the VendorID number but be right justified so the positions and zeros will always have no more thank 10 spaces so the first number is VEN0000000001 and 999 would be VEN0000000999 if the number grows to VEN0000099999 then the number of zeros goes down but the number of spaces and numbers after the VEN are the 10 spaces.
Ok I understand. A couple of questions, is this vendor number being placed on a form field? Is this form opened by a few people at once or only one person?
And to be clear it needs to be VEN + 10 digits. Right? You said one thing above and typed another as an example.
And to be clear it needs to be VEN + 10 digits. Right? You said one thing above and typed another as an example.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
William sorry I made a mistake but yes max string lenght is 20 long.
that includes VEN + 17 Zeros and numbers
that includes VEN + 17 Zeros and numbers
ASKER
Sorry hnasr You are correct with my example.
So it looks like this:
A = "VEN"
B = "000000000000000000000"
C = [VendorID] - this starts at 1 and goes to 9999999
The value now looks like "VEN00000000009999999"
The number of zeros decrease as the number of 9's increase.
So it looks like this:
A = "VEN"
B = "000000000000000000000"
C = [VendorID] - this starts at 1 and goes to 9999999
The value now looks like "VEN00000000009999999"
The number of zeros decrease as the number of 9's increase.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to everyone. I will review and let you know in the morning. Got to go and pick up something before the store closes.
ASKER
All solutions offered except for the Dot Net, sorry I have not got into Dot Net yet. This is in an Access 2010 database system and I have not yet moved it to an Internet or Intranet offering but thank you for your insight. William Blake your solution works great when I want to use a button to run the code. CholesDad The one line works great too. Hnasr your code works great as well. You all have done a great job for me.
I forget to add that what I needed was a way to do these in a one line by running a query but I figured that out when I remembered a small utility I built a year ago to develop a modulo and then call it within the query. Thanks all
I forget to add that what I needed was a way to do these in a one line by running a query but I figured that out when I remembered a small utility I built a year ago to develop a modulo and then call it within the query. Thanks all
Welcome!
IE:
ABC0000000001
ABC0000000002
ABC0000000003
[...]
ABC00000000999999
Is this what you are looking for? If so that should be a pretty easy function to write.