Rob,

My apologies, but when I use a function by means of a wizard, I dubble click the fieldname and select the field from the table. Which normaly works.
Thanks for your help in this.

rgrds,

Paul.

Paul,

When double clicking the names of the fields, it creates a reference to the field in the query expression (result)
What the data provider then does when producing the recordset, is to place the values of these fields for the current record in its place.
This is why it is causing you problems, because we do not want the VALUES of the fields, but the NAMES.

you must enter the name (and only the name) in quotes (") as previously advised, then it will work.

Do not use the wizard to create it, just type it in manually.

Regards,
Rob

Hi Rob, I have been testing this function but it does work pretty slow? I have a dataset of approx 129000 records of which I selected 7 records it took about 7 - 10 seconds to create the string for those 7 records imagine 129000 record. Is there another alternative?

Rgrds,

Paul.

Paul,

Sadly, this method can be slow, especially with large amounts of data and over a networked environment.
7-10 seconds seems pretty slow for only 7 records, are you working with networked data?

Regards,
Rob

Hi Rob,

The data is from a networked environment. Would it perform quicker when the data has been sorted already on number (criteria) and when it does not find the same criteria for example previous number <> number then the full concatenation on the last record based on the Max of sequence is created.
Alternatively, I don’t mind when you see the build up of this concatenation as shown in my example at the beginning of this thread.

I could not make this code working:

rs = db.recordset("Your table here")
       PrevNum = null
       textline = ""
       for r=1 to nRecords
          if rs("Number") <> PrevNum then
             if PrevNum is not null then
                create new record with PrevNum & textline
             PrevNum = rs("Number")
             textline = rs("text")
          else
             textline = textline & rs("text")
          end if
       next r

Best regards

Paul.

paul,

Sorting the data is unlikely to offer anything in the way of improvement, the speed hit comes from the fact that the recordset object is created and opened every time the function is called.

As for the code on your previous post, what are you trying to do? Create a table containing the values?
If you want to use the concatenated string in VBA, create your recordset (as you normally would) and when you want the concatenated string, just add a direct function call (i.e. 'ConcatRecord(1,"number","textline","test")').

I hope this helps,
Rob

Hi, I have a question:

Is there a way to look up just a part of the "number" column? I know it would be useless in the example you provide since you would end up with entries like 11 121 and so on but I need this for my tour operator database.

Lets say I want to look up and concatenate e-mail adresses of operators with "Business" specialization. This works if they are just Business oriented but if their entry looks like "City Breaks, Business, Cycling", they are not included in the selection. I have tried fixing this by plugging "*" & ... & "*" in various places inside the code or the query but did not succeed.

By the way, my Access 2000 refuses to accept the ConcatRecord(1,"number","textline","test") format, I have to put it in as ConcatRecord(1;"number";"textline";"test")

Thx for the help,

VBA noob Mike

Mike,

Yeah it is possible to get parts (substrings) from a column. However, it depends on how data is stored, do you want the first 'x' characters? all characters before "," ?

In terms of only getting addresses for people with certain specilisations, you should filter the data down as much as possible using direct SQL rather than letting VBA functions perform the "grunt" work. So select only the data you want to concatenate via SQL selection, and then being processing the strings.

As for your point on your Access version not liking the function call, you must have something wrong somewhere.. a comma is the standard character for seperating parameters, can you post your current code?

Regards,

Rob
The Developing Developer
Currently Working Towards: MCAD C#
My Blog: http://www.robzyc.spaces.live.com
<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>