Add strings altogether

[hortense76]

I have a problem which I think it can be split into different ones.

I want to create an event which automatically fill in the textbox AllText in a Form (based on Table1) with the SUM of ALL text strings (in this case all of only one character) found in Field1 of all connected records of Table2.

• -1st of all I don't know how to SELECT these records
• -2nd, I don't know any string function that returns a string which is the simple connection of all texts from one string Field of selected records, or even simplier from a "vector" or "list" (or something like this, I'm quite new with VB) of strings.

Simpler example:
I have a table of "Letters" ("name","type" fields) and I want to put together a selection of this ones (only of the "vowel" type) in text field of my form/table "LettersByType", getting the text from field "name", with the result of "aeiou"... sounds rathers simple, but believe me I couldn't neither of the points.

Thank you

 

[gemma-the-husky]

write a query that returns the records you want

then in code, use a recordset rst so that

rst = openrecordset(qryname)
rst.movefirst
while not rst.eof

rst.mov

 

[gemma-the-husky]

sorry, clicked tab and it sent

rst = openrecordset(qryname)

mystrg = ""
rst.movefirst
while not rst.eof
mystrg = mystrg & vbcrlf (or any separator) & rst!requireddata
rst.movenext
wend

note - as access is unordered, the strings may not be in any particular order. if you need to process the records in a particular order, you may need to set a key before iterating the rst



rst.close

 

[hortense76]

thanks gemma,
your suggestion seems perfect on the side of creating the string.
Anyway I have a problem with the recordset object and with the openrecordset method: when I try to open my query as

CurrentDb.OpenRecordset("MyQuery")

(or in any other way) it calls the debug for (translated from Italian) "Bad use of property", anyway... WHICH PROPERTY? it's a METHOD! ??????

I found a page who seems to explain something VERY similar to my needs
(http://www.blueclaw-db.com/concatenate_multiple_records_one_field.htm)

anyway this is much more complex because it uses the DAO way and everything goes bigger and bigger and bigger (and still doesn't work for other reasons: perhaps because my version is MS VB 6.3) and everything for SUCH A SMALL THING!!!!

Perhaps using this d***ed RecordSet obj is not the right way: should I read record by record in a simpler way? (I've NO NEED to have it FAST and PERFORMING!!!)
Any IDEA?
HELP!!!

 

[hortense76]

thanks, done it with Dlookup by serializing query.