Link to home
Create AccountLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Build a string from a select statement by looping through it.

I have a select statement that returns one or more organization IDs.  I need to populate an In operators - for example:  In ("OrgID_1", OrgID_5", "Org_ID15") from a loop through teh statement.  Ih have the below, but can't quite get it to loop with quotes.  Also, sometines there is only one OrgID returned.

Sandra

strSelect = "SELECT OrgID From Organization WHERE org_manager_user_ID = '" & Me.lblLoggedInUser & "' "
Set rst = CurrentDb.OpenRecordset(strSelect)
rst.MoveFirst
Do Until rst.EOF
        strOrgID = rst!OrgID
   
    strOrgID = strOrgID + rst!OrgID
    Debug.Print strOrgID
rst.MoveNext
Loop
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

<air code>

Dim sSQL as String, sOrgs as String

sSQL= "SELECT OrgID From Organization WHERE org_manager_user_ID = '" & Me.lblLoggedInUser & "' "
Set rst = CurrentDb.OpenRecordset(sSQL)

sOrgs = ""

'Loop through the recordset, adding the orgID and a comma to the string
rst.MoveFirst
Do Until rst.EOF
        sOrgs = rst!OrgID & ", "
        rst.MoveNext
Loop

'Whack the final comma and space
sOrgs = Left(sOrgs, Len(sOrgs) - 2)
Avatar of Norie
Norie

Try this.
strOrgID = strOrgID & Chr(34) & rst!OrgID & Chr(34) & ","

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Sandra,
wondering why you need to concatenate the sOrgs string, what's the next step?
Select * from SomeTable where SomeID In ("OrgID_1", OrgID_5", "Org_ID15") ?

Might be possible to write one SQL statement that gets the end results you want right off the bat...

Incidentally, I reckon "OrgID_1" may qualify as being IN "Org_ID15".

Alan
Avatar of Sandra Smith

ASKER

This seems to work.  I needed to do it this way as each time the procedure is run it needs to be passed to a completed select statement as the where clause.  The statement is a little messy (I inherited this) and needed to distill down to naming a WHERE variable that included in IN clause - I just needed to figure out how to get the Org id.

Thank you all.

Sandra