Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-03
13
Medium Priority
?
302 Views
Last Modified: 2014-09-04
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
0
Comment
Question by:frank_guess
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 

Expert Comment

by:William Blake
ID: 40302059
I am not sure I understand what you need.  Are you saying you want something that will automatically generate and store a string value of any three letters and 00000000 and 01 -> 999999
IE:
ABC0000000001
ABC0000000002
ABC0000000003
[...]
ABC00000000999999

Is this what you are looking for?  If so that should be a pretty easy function to write.
0
 

Author Comment

by:frank_guess
ID: 40302094
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.
0
 

Expert Comment

by:William Blake
ID: 40302143
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 800 total points
ID: 40302148
"no more than 20 long"
The maximum string is ABC00000000999999 which is 17 long.

Try this in the immediate window:
The idea is to concatenate the 3 characters + 8 0s + right 6chars (from 5 0s + the number)
? "ABC" & "00000000" & right("00000" & 99,6)

To use it in code: Try the following statements  in immediate window.

c="ABC"
z="00000000"
f="00000"
n=99
? c & z & right(f & n,6)
0
 

Author Comment

by:frank_guess
ID: 40302193
William sorry I made a mistake but yes max string lenght is 20 long.
that includes VEN + 17 Zeros and numbers
0
 

Author Comment

by:frank_guess
ID: 40302201
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.
0
 

Assisted Solution

by:William Blake
William Blake earned 400 total points
ID: 40302205
Ok, here is some VBA that might get you started.  It does a DMAX lookup to get the highest Vendor ID in a table (I assume you have some sort of vendors table).  Then strips off the VEN prefix and converts the id to a number and adds one.  It then converts it back to a string and gets a string length which using an if else if statement adds the apppriate number of zeros between VEN and the Counter.  Let me know if this goes in the right direction.  If you need it to appear on a form, that can easily be done too (although I might want this function to fire when the form is submitted to make sure the vendor ID remains unique.

Dim vendorID As String
Dim vendorIDNum As String
Dim idVal As Integer
Dim vendorIDLen As Integer
' get the max vendor id from a table
vendorID = DMax("vendorID", "table1")
' split off the ven prefix
vendorIDNum = Mid(vendorID, 4)
'convert to a number
idVal = CInt(vendorIDNum)
' add one to it
idVal = idVal + 1
'convert back to string
vendorID = CStr(idVal)
'check the length
vendorIDLen = Len(vendorID)

If vendorIDLen = 1 Then
    vendorID = "VEN000000000" & vendorID
ElseIf vendorIDLen = 2 Then
    vendorID = "VEN00000000" & vendorID
ElseIf vendorIDLen = 3 Then
    vendorID = "VEN0000000" & vendorID
ElseIf vendorIDLen = 4 Then
    vendorID = "VEN000000" & vendorID
ElseIf vendorIDLen = 5 Then
    vendorID = "VEN00000" & vendorID
ElseIf vendorIDLen = 6 Then
    vendorID = "VEN0000" & vendorID
ElseIf vendorIDLen = 7 Then
    vendorID = "VEN000" & vendorID
ElseIf vendorIDLen = 8 Then
    vendorID = "VEN00" & vendorID
ElseIf vendorIDLen = 9 Then
    vendorID = "VEN0" & vendorID
ElseIf vendorIDLen = 10 Then
    vendorID = "VEN" & vendorID
End If

Open in new window


I did the above while I thought it was 10 digits between the VEN and counter.  You will have to adjust the If else if to compensate.
0
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 800 total points
ID: 40302313
You can do it in one line

VendorID = "VEN" + right("0000000000000000" & CStr(idVal),17)

This will add the right most 17 characters of 0000000000000000000000xxx to the initial string
0
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 800 total points
ID: 40302316
In .net you can do it with the .Tostring also

VendorID = "VEN" & idval.tostring("00000000000000000")
0
 
LVL 31

Accepted Solution

by:
hnasr earned 800 total points
ID: 40302323
Try this:
A = "VEN"
B = "00000000000000000"
You may use:
B = string(17,"0")
C= [VendorID]
txt = A  & RIGHT(B & C , 17)
0
 

Author Comment

by:frank_guess
ID: 40302373
Thanks to everyone.  I will review and let you know in the morning.  Got to go and pick up something before the store closes.
0
 

Author Closing Comment

by:frank_guess
ID: 40303596
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
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40303804
Welcome!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question