ACCESS VBA to Randomly Concatenate Strings in Records with Character Limit on Resultant String

Cherie Woodward
Cherie Woodward used Ask the Experts™
on
I want to create a search term generator using vba.  I have a table with several records each containing a different search phrase, [Search].[Phrases].  I want to randomly concatenate the records with a space between each to generate one long string that is close to but not exceeding 250 characters long.  Can anyone help on how I would do this with VBA in ACCESS 2016?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer

Commented:
Are you sure you want to separate them with a space?  Do any of your phrases contain spaces, and if they do, would that cause a problem?

Also, what's the purpose of "randomly concatenating" the phrases together?  Why not just take the phrase in each record as you go thru the recordset and append it to the end of your string?

Sorry for all the questions, but this just seems too easy to need any help with it..... There's got to be more to it....
John TsioumprisSoftware & Systems Engineer

Commented:
Its a bit vague to say the least but  a combination of Rnd and Len will give you what you need.
Something like
Function ConcRnd()

Dim threshold as Integer
Dim currentRecord as integer
Dim tmpSearch as String
Dim rst as DAO.Recordset
threshold =250
set rst = currentdb.openRecordset("YourTable")
Do
rst.moveFirst
currentRecord  = Rnd(1) * rst.Recordcount
DoCmd.GoToRecord , , acFirst,currentRecord
tmpSearch = rst.Fields("Phrases") & " " & tmpSearch
Loop until  len(tmpSearch) > threshold -len(rst.Fields("Phrases") )
ConcRnd = tmpSearch
End Function

Open in new window

Mark EdwardsChief Technology Officer

Commented:
Cherie:  The code in the form in the attached file goes thru the Name field in the MSysObjects table and builds a string with the names separated by a space unless the addition of another name pushes it past 250 characters, then it stops adding names.

Hope this helps...
Build--A-String-Bear.accdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial