I need a query that collects words from a column and turn it into a string for a forum. Can it be done? (1 Viewer)

[rgarber]

Back history: This is for a form based on a parent table with another table associated with it called Tags that I've entered words to describe each parent record.

The issue:
Let's say a query has the result where it yields the following four words from a single column.

3Dfx

tutorials

composite

editing

Can I get Access to string those four words together so the string can be placed into a field on the form? Something like this:

ex: (Label: String
Tags: [3Dfx] [tutorials] [composite] [editing]

Each record of the parent table would have a different number of words associated for that record so it wouldn't always just be four words.

I don't have much room on the form to place the datasheet so I'm thinking this might be one way to display the tags list for the record. Thanks!

 

[Ranman256]

this will collect a values into a list. You decide where to put it.

   'return a list all field results
Public Function getListOfVals()
Dim rst
Dim vWord, vList
const dbOpenSnapshot = 4

Set rst = CurrentDb.OpenRecordset("select * from qsMyQuery", dbOpenSnapshot)
With rst
   While Not .EOF
       vWord = .Fields(0).value & ""
       vList = vList & Trim(vWord) & ","
       
       .MoveNext
   Wend
End With
Set rst = Nothing
getListOfVals = vList
End Function

 

[rgarber]

That was fast! And I thank you for that. Maybe I should have added I'm fairly new to Access recently and first, I wouldn't know where to put the code you wrote not to mention it's Greek to me. Anyway you can explain some of that?

 

[DHookom]

Here is a similar generic function that contains some comments regarding usage.
similar generic function

 

[Uncle Gizmo]

You might find this blog on my website about concatenation useful:-

Display Field Data Horizontally – Nifty Access

www.niftyaccess.com

 

[June7]

This is an extremely common topic. More references:
https://www.access-programmers.co.u...tenate-records-from-a-query-in-1-line.333069/
https://www.accessforums.net/showthread.php?t=90489&page=2
https://stackoverflow.com/questions...tems-in-a-third-field-using-a-comma-separator

Need to place code in a general module. Call function from query or textbox.

Here is an overview of suggested code: each of these custom function procedures opens a recordset object, loops through its records, concatenates field values, returns constructed string to calling entity. That's an example of pseudocode - an outline of the steps of an algorithm.

A line-by-line translation of code is really beyond scope of this forum. Need to study a tutorial on VBA programming. If you have any programming background, basic concepts should be transferrable and picking up VBA fairly simple. We can't provide a comprehensive tutorial within a forum thread so if you have NO programming skills start with:
https://support.microsoft.com/en-us...gramming-92eb616b-3204-4121-9277-70649e33be4f
https://www.bing.com/videos/rivervi...88E1F1D9B2806&FORM=VIRE&ajaxhist=0&ajaxserp=0

 

[Gasman]

That was fast! And I thank you for that. Maybe I should have added I'm fairly new to Access recently and first, I wouldn't know where to put the code you wrote not to mention it's Greek to me. Anyway you can explain some of that?

Perhaps start reading this then?
Another is here
Regardless of what you use, you have to do the work yourself and learn how to use it. The first link should help.

 

[Uncle Gizmo]

A line-by-line translation of code is really beyond scope of this forum.

The video on my website does go through the code line by line explaining it...

Display Field Data Horizontally - Nifty Access​

In the video you can see the code, and you can see where I insert a comma between the words. You can remove this comma, leave the space in, and then you will have a space between each word... That should build your sentences for you...

 

[Gasman]

I think some people expect it all to be written for them and handed to them on a plate.

 

[Mark_]

Before you get too far into your "Tags", do a search on "Many to Many Relationships". You will want a master file with what tags CAN be assigned, then you will use a table that is a child to both the "Tags" table and your current parent table to link the two together.

Once you start adding things like tags, users will want to be able to filter your parent on specific tags. If you don't have their values saved in a table you run into massive issues when people can't type or can't spell. If you really want to be clever, you can also add an "Order" field in your "Tags" table so they can be ordered in the way you are showing.

 

[June7]

Lookup table is one way to go. I have a database that required some text fields to be free-form data entry but there was enough similarity in the data entered they are almost duplicates but not quite. So I used the input field as source for a combobox to "assist" users with data input but not limit them to a specific list. There is no lookup table to maintain. Example of data:

Source
Hamilton Const. / ARB
Hamilton Const. / Crown Pt. Pit
Hamilton Const. / Metco Pit
Hamilton Construction
Hamilton Construction
Hamilton Quarry
Harris River Pit
Harris Sand & Gravel
Harris Sand & Gravel

RowSource of combobox: SELECT DISTINCT Source FROM SUBMIT WHERE Source Is Not Null;

I have several fields like this. If I thought this db would get massive I would probably go to maintaining lookup tables.

 

[arnelgp]

see Module1 for the ConcatRelated() function and
see Query1 for the result.