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

Random Alphabet combination from a given string

P: 171
Is there a way we can generate random alphabet combination from a given string? I am looking for a 3 letter combination from a given string by using vba.
Before or after generating it should validate as a unique combination which is not available in another table?
Hope someone can help me

Oct 27 '15 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 5K+
P: 5,397
If you mean, is there a built in function, yes and no,
yes because you can (in newer version of Access) use an autnumber field set to the "Replication ID" data type that returns random strings such as:

No, in that there is no built in function that will either generate a random string by itself nor from a provided string.

Can you program one, yes, indeed I have such a function. :)

+ I based mine on the static string of:
Then length is 38
+ Standard randomize command to ensure that each round starts with a new random number seed
+ Standard method of producing the random number, one such being: CInt(Int((n * Rnd()) + 1)) so in the above would be CInt(Int((38 * Rnd()) + 1))
+ Use the Mid() function to return a single character from the string.
+ I have one parameter passed to this function which is the length.
I loop the number of times, concatenate the result from each loop with the prior

I even have one that will calculate a check digit to validate a provided "serial number;" however, that is a different thread. (-.-)

We'll be happy to take a look at your version of the code.
Oct 27 '15 #2

P: 171
Hi zmbd
Thank u for the reply.
I have found a code with which I tried but not getting the desired result. I am posting it here so you can help me to modify it according to my requirement. The code actually taking the first 2 characters and then adding another one from the alphabets. I need the code to select the first 2 from the given string then select randomly from another 1 from the given string only. Hope I have explained well
Expand|Select|Wrap|Line Numbers
  1. Sub GenerateRandomAlphabetsFromGivenText()
  2.    Dim s As String * 3 'fixed length for the resulting string
  3.    Dim n As Integer
  4.    Dim ch As Integer 'the character
  5. '
  6.    For n = 1 To Len(s)
  7. '
  8.       Do
  9.          ch = Rnd() * 127
  10.       Loop While ch < 65 Or ch > 90
  11. '
  12.       s = "test vbtr"
  13.       Mid(s, n, 1) = Chr(ch)
  14.    Next
  15. '
  16.    Debug.Print UCase(s)
  17. '
  18. End Sub
Oct 28 '15 #3

Expert Mod 5K+
P: 5,397
A) If you are going to call this to return the value within another sub-routine or via SQL then you should make this a Function(). For testing, the debug.print is ok; however, with a function you could <ctrl><g> then type ?FuntionName(AnyRequiredParameters) and have the result returned in the immediates window.

B) Line 2: not sure that you really need to explicitly declare the fixed length. You're controlling the length with your loop and appending script - unless you are going to alter the string length later, in which case, I would place that in the parameters of the Function(zDesiredLen AS Long) and then pass that into the function for use in the loop
B2) Line 2 is in conflict with Line 12.
C) You need a holding string variable to hold your returned characters as you build the string.

D) Line 6: see (B)

E) Line 8 - 10: The Do...Loop here isn't needed. Use the hint I provided in my first post for returning a random number between 1 and a given upper limit.
E1) If you are not going to hard code the reference string, Line12, then you could pass this into the Sub/Func so that you have Function(zDesiredLen AS Long, zRefString as String) then use the len() function to set your upper limit on the random number
E2) you should include Randomize in your code, I'd suggest placing it just before your For..Next loop
F) In post #3 you stipulate that your first two characters should come from the reference string and then the third from "a-z"
F1) Alter your For..Next loop to only loop twice, building your first two characters from the reference string (or if you desire, you could alter this to loop n times based in the passed in parameter).
F2) Using the hint on Random number generation, set your bottom limit to 65 and the upper limit to 90 (note the hint provided give a result from 1 to upper limit... ) use the Chr() to convert to the String character and append to your current string

So your code could look something like
Expand|Select|Wrap|Line Numbers
  1. Function ExampleAirCode(zDsrdLn as Long, zPick1stN as Long, _ 
  2.    zTestStr as String) As String
  3.    ... Declared variables
  4. '
  5.    ... validate and pass in zDsrdLn, zPick1stN and zTestStr
  6. '
  7.    ... get the length of zTestStr
  8. '
  9.    ... For...Next loop based zPick1stN value to return the first N characters from the zTestStr. Use the Length of zTestStr as the upper limit on the rnd() and the Mid() to return from the zTestStr
  10. '
  11.    ... now use the rnd() with the lower/upper bounds for 65 thru 95, convert, and append to your built string.
  12.    ... could add a loop here to append more than one!
  13. '
  14.    ... now ExampleAirCode = builtstring.
  15. '
  16. Exit Sub
  17.    ... should have error trapping setup
  18. End Sub
That should get you started.

Post back after you have tweeked the code.
You might also want to bookmark > Before Posting (VBA or SQL) Code for the trouble shooting advice.

Just a to clarify this from post#2:
Expand|Select|Wrap|Line Numbers
  1. + zRefStr="1the2quick3brow4nfx5jum6psv7er8laz9ydg"
  2. + zUpper=len(zRefStr)
  3. + zPosition=CInt(Int((zUpper * Rnd()) + 1))
  4. + zTmp = zTmp & mid(zRef,zPosition,1)
zrefstr could be set to a passed in parameter or hardcoded as shown above; hence, the Len() to set the upper limit on the random number generator. In post#2 I showed the upper limit hardcoded to 38
Oct 28 '15 #4

P: 171
Hi zmbd
Thank you for the reply. I just got your message. I will try the code and reply you with the result
Oct 28 '15 #5

P: 171
Hi zmbd
Sorry for a late reply. I was trying with the same code I have posted because the code you have supplied was difficult for me to understand and make the changes to get a result what I need.
I have a form where I will enter a staff name and from that name I need to get a random combination of 3 letters (first letter should be the first letter in the staff name) and the other 2 letters can be selected randomly from the staff name. After generating the 3 letter, cross check this 3 letter against a table for any duplicates. If there is already same 3 letter then the process will continue till it can generate a unique 3 letter and show the result.
Hope I explained well. Pls help
Nov 8 '15 #6

Expert Mod 5K+
P: 5,397
+ The cross check against a table is fairly easy.
You can create an index against the field with unique set to true, and/or, you can use a simple query with the WHERE clause appropriately defined.

In the lab, I most often use both the indexed method as I have to ensure that all of the sample serial numbers are unique and due to regulatory requirements these are never purged. I use the query just to avoid error trapping an index violation before appending/updating the records.

+ zPosition=CInt(Int((intLargestNumberToReturn * Rnd()) + intLowestNumberToReturn))

So if you want between 0 and 100
zPosition=CInt(Int((100 * Rnd()) + 0))
(of course, the zero isn't required, it's just there for illistation)

So if you want between 65 and 95
zPosition=CInt(Int((95 * Rnd()) + 65))

+The code you posted will prove, IMHO, more troublesome than you expect; however, post your latest and we'll revise from there.
Nov 8 '15 #7

P: 171
Dear zmbd
please note that I am looking for only alphabet combinations from a given string of alphabets. If "SELECTION" is the given string it should check the possibilities of starting from the first two characters "SE" and start adding the next letter "L" (So the 3 letters are "SEL") and cross check with a table if it is unique. If not then try with the next letter that is "E" so the 3 letters are now "SEE". Try until it can find a unique combination. Hope my request is clear. Please advise
Nov 9 '15 #8

Expert Mod 5K+
P: 5,397
I understand what you want.
I have provided the basic outline of the required code all you need to do is fill in the blanks.

I'll happily help trouble-shoot your posted code; however, I currently do not have the time today to write the code for you.
Nov 9 '15 #9

Post your reply

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