I want to set a variable (for display purposes only ), showing a users assigned deparments.

Is there a Jet SQL function to return a result as a comma separated list or do I need to loop thru the recordset?
If so, what an efficient way to fetch this.


The Query below gets me the list in rows
SELECT tblDepartments.Department_Name From tblDepartments where tblDepartments.ID IN (Select tblUserDepts.Dept_ID from tblUserDepts where tblUserDepts.User_ID = 20);

but I can't help but think I could in SQL as a little comma separated list.. so the function below could return the string.


roughly....

Public Function GetEmpDepts() As String
Dim mysql As String

mysql = "SELECT LIST tblDepartments.Department_Name From tblDepartments where tblDepartments.ID IN (Select tblUserDepts.Dept_ID from tblUserDepts where tblUserDepts.User_ID = 20);"


GetEmpDepts = mysql

End Function


... can anyone help??
Thanks!

I'm not sure what you are trying to accomplish here. but I am unaware of any built in function
that would return a comma seperated value. IF this is for display purposes then I would assume that it
is on a form where the user might select a combo box of users and then there assigned departments
are shown in a label with the data?
If so then pass the user ID into the first SQL statement, then loop through the result adding the information into
the label using VBcrlf after each line CRLF stands for cariage return line feed.

HTH
DBear

nope.. I just want to show a list in the header that shows the employess access departments...
for example:

John Smith: Administration,Cutomer Service,Payroll

when the user logs I want to fetch this list as a string to show on every page.

in other databases there are funtions like

SELECT LIST
SELECTLIST
or...
SELECT DISTINCT LIST(Home_State) AS All_State_Values
FROM Sample.Person
ORDER BY Home_State

that kind of thing.. but I can't find any post on doing it in Jet-SQL.

Anyone know how?

There's a lot I don't know but I'm pretty certain there's no recordset list function or Jet-SQL list function. What's the big deal with lopping through the recordset and building the string that way? It would take like maybe 4 short lines of code.

Well I just wanted to know if I could do it in Access as its a very useful function.

Ok. i need help .. how do I write this? I want to send the userid into the public function and return the comma separated list as a string... I just realized I've never written a recordset loop like this... can anyone show me how to do it properly. It will be a very useful example. Thank you!

I think this will work.

Dim rs as recordset
Dim strIDlist

set rs = DoCmd.RunSQL mysql
while not rs.EOF
strIDlist = strIDlist + rs("Department Name")
rs.movenext
if not rs.EOF then
strIDlist = strIDlist + ", "
End if
wend

GetEmpDepts = strIDlist


so your SQL statement should be the same without the LIST keyword, I believe

Great! Thanks! you the bomb

I forgot those '+' s should actually be ampersands ( '&' ), I believe.

strIDlist = strIDlist & ", "

Yep.. figured that.
again..
Utter thanks.

= )

This is what wound up working.
Please let me know if there is anything unessary or if can be pared down any more.
Thanks again!

Technically this code will run faster, but you will never notice a difference:

Public Function GetEmpDepts() As String
Dim rs As Recordset
Dim strIDlist
Dim mySQL As String
mySQL = "SELECT tblDepartments.Department_Name From tblDepartments where tblDepartments.ID IN(Select tblUserDepts.Dept_ID from tblUserDepts where tblUserDepts.User_ID = " & gUserID & ");"

Set rs = CreateObject("ADODB.Recordset")
rs.Open mySQL, CurrentProject.Connection

if Not rs.EOF then
strIDlist = rs("Department_Name")
rs.moveNext
End if

While Not rs.EOF
strIDlist = strIDlist & ", " & rs("Department_Name")
rs.MoveNext
Wend
rs.Close
GetEmpDepts = strIDlist
End Function

It's only faster by taking the extra IF test out of the While Loop.
If you had to loop through millions of records you might notice a difference, but still not a huge one.