Concatination

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,
I am trying to concatinate a list of names from a query.
This is Access 2003
Query Name: NamesRelated
Field inQuery: Names

I have the following code and I get no results

Dim DBS as Database
Dim RS as DAO.Recordset
dim x as string
x=""
Set DBS = CurrentDb
Set RS=DBS.OpenRecordset("NamesRelated")

Do Until RS.EOF
x=x & RS!names & ","
RS.MoveNext
loop

To test, I use a message box to display the text from x.
I get nothing every time. The Query runs just fine.
What have I missed here?

Thanks

Brian
 
Brian said:
I am trying to concatinate a list of names from a query.
This is Access 2003
Query Name: NamesRelated
Field inQuery: Names

I have the following code and I get no results

Dim DBS as Database
Dim RS as DAO.Recordset
dim x as string
x=""
Set DBS = CurrentDb
Set RS=DBS.OpenRecordset("NamesRelated")

Do Until RS.EOF
x=x & RS!names & ","
RS.MoveNext
loop

To test, I use a message box to display the text from x.
I get nothing every time. The Query runs just fine.
Click to expand...


Is the MsgBox in the above procedure where X is a local
variable that is discarded when the procedure exits?
 
Yes, x is just local and is discarded.

Marshall Barton said:
Is the MsgBox in the above procedure where X is a local
variable that is discarded when the procedure exits?
Click to expand...
 
Brian said:
Yes, x is just local and is discarded.
Click to expand...


Sorry, poorly worded reply.

I meant to ask if the msg box was also in he same procedure?
You didn't post enough code for me to be able to tell where
it was or how else you're using the variable X.
 
Hi

Off the top of my head just a couple of quick things to check

1. make sure RS contains records. Use the msg box to display the RS count.
Or alternatively put a breakpoint within the loop and make sure it gets
there.
2. try RS.movefirst before the loop. This is just in case your recordset
ptr is not at the beginning of the recordset (although it should be but its
worth checking anyway...)

Both of the above are designed to eliminate the issue of RS being empty, or
not looping through the recordset as you would expect.

To my mind the above is the most likely IF we can take your comment
literally that the msg box displaying x contains nothing, it should at least
contain the "," characters if the loop was entered.


If the above does not highlight anything then put a msgbox in the loop and
output RS!Names each time. This will show you the contents of that field.
Is this the field you are wanting to output? Does each record actually
contain any data?

Regards
AC
 
Hi

Mr Barton was more asking where you make the msgbox call from, just in case
you are calling it from a different routine than the one you posted.

Is it right underneath the code *in the same routine*? If it is called from
elsewhere then that may be the cause of the problem (depending on how you
pass the x variable around).

If you add the following code to the top of your VBA:
Option Explicit
do you get any errors?

Regards
AC
 
Back
Top