Creating a comma delimited LOV from multiple rows into one row

[Fattire]

Trying to dynamically create a string in the WHERE clause of a SQL statement:

lets call the table 'tbl_example', column is called 'data_id'. Here's the data, 4 lines:

DATA_ID
1111
2222
3333
4444

turn this data into a string so I can assign it to a variable and plug it into a SQL statement - looking like this:
'1111','2222','3333','4444'

 

[MajP]

faq701-3499

 

[dhookom]

Or this one faq701-4233

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Fattire . . .

If you use an [blue]In Clause[/blue] with a subQuery/SQL, you won't have to bother concatenating. Example:

[blue]   SQL = "SELECT * " & _
         "FROM TableName " & _
         "WHERE [DataID] In (Select DataID " & _
                            "FROM TableName " & _
                            "WHERE (YourCriteria);"[/blue]

See another example in my post here: thread701-1464141

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Fattire]

Thank you for all the suggestions...

Here's what I used eventually, works fine...

    Do While Not rec1.EOF
    varCustNum = rec1!customer_number
    varCustNumStr = varCustNumStr & varCustNum & ", "
    rec1.MoveNext
    Loop