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

x
Solved

# Access 2013 - Visual basic code

Posted on 2015-02-05
Medium Priority
279 Views
Last Modified: 2015-02-09
My OS is win 7 prof 64 bit and I use  Office 365, Access 2013.  I would like to know what is the syntax to rearrange a string of text :
Eg : a string like : 1234TqRi*(Ka
The string will consist of CAPS, small case, numbers and any key from the keyboard.
To be rearranged at random using vb code.
Thank u.
0
Question by:jegajothy
[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
• 4
• 3
• 2
• +1
10 Comments

LVL 12

Expert Comment

ID: 40591017
you can put each letter in array and make loop for len of the string you want
that takes random number of the array index usin RND function
0

LVL 58

Expert Comment

ID: 40591023
There's nothing built-in to do that.   FarWest's idea is a good one and can be accomplished with all the usual string functions:  Left(), Mid(), Right(), etc and more than likely, you'll use Mid() the most.

Jim.
0

Author Comment

ID: 40591024
In response to FarWest, thank u for your suggestion, but if u can spare the time to give me a sample code.  Thank u.
0

LVL 18

Expert Comment

ID: 40591043
This doesn't use FarWest's array idea (I was writing it before the first response) and is probably not as efficient, but it works...

``````Function RandomiseString(inputString) As String
mystr = inputString
myStrLen = Len(inputString)
resultstring = ""
Do Until Len(resultstring) = myStrLen
random_number = Int(Len(mystr) * Rnd) + 1
'Debug.Print random_number
resultstring = resultstring & Mid(mystr, random_number, 1)
mystr = Left(mystr, random_number - 1) & Right(mystr, Len(mystr) - random_number)
Loop
Debug.Print resultstring
End Function

Sub TestIt()
Debug.Print RandomiseString("SwitchThisPitch")
End Sub
``````
0

LVL 12

Expert Comment

ID: 40591059
here is the code
``````Private Sub Command5_Click()
Dim ar() As String
Dim rstr As String
Dim ii As Integer
Dim rn As Integer
ar = Split("a,b,c,d,N,R,U,r,\$,/,T", ",")
Randomize
For ii = 1 To 10
rn = Int(11 * Rnd())  '10 is array lenght
rstr = rstr + ar(rn)

Next
Debug.Print rstr

End Sub
``````
0

LVL 18

Accepted Solution

Simon earned 2000 total points
ID: 40591066
Sorry, had to tweak mine. I hadn't set the return value for the function:
``````Function RandomiseString(inputString) As String
mystr = inputString
myStrLen = Len(inputString)
resultstring = ""
Do Until Len(resultstring) = myStrLen
random_number = Int(Len(mystr) * Rnd) + 1
resultstring = resultstring & Mid(mystr, random_number, 1)
mystr = Left(mystr, random_number - 1) & Right(mystr, Len(mystr) - random_number)
Loop
RandomiseString = resultstring
End Function

Sub TestIt()
Debug.Print (RandomiseString("1234TqRi*(Ka"))
End Sub
``````

You can feed strings of any length to the function to return them re-ordered randomly.
0

Author Comment

ID: 40597440
thank u everyone for your inputs.  SimonAdept's solution fits into my design, thank u very much.
0

Author Closing Comment

ID: 40597442
SimonAdept's solution fits into my design, thank u.
0

LVL 12

Expert Comment

ID: 40597988
Final hint, don't forget to call Randomize to link the RND function call with system clock, so probability to have the same output twice will be close to 0
0

Author Comment

ID: 40598224
In response to FarWest, my soluion has been a crude and dirty one, and hope it gives me the result that I want.  i think I should put up the final code to see what the experts think of it.  Thank u again.
0

## Featured Post

Question has a verified solution.

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

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in aâ€¦
###### Suggested Courses
Course of the Month7 days, 22 hours left to enroll

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

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