Re: Build an IN clause from SELECT records

paii, Ron wrote:
<troy_lee@xxxxxxxxxxx> wrote in message
news:43ef6422-82b4-4e9b-9a9d-331071580664@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I want to build a string from results of a SQL SELECT statement. This
string would be used for an IN clause in a subsequent SQL query.

How do I return the results of this query to a string with each record
separated by a comma so I can use them in an IN clause?

[CODE] "SELECT [Component] " & _
"FROM cm_dbo_bomstructure " & _
"WHERE [ParentPart] = """ & s & """;"[/CODE]

Thanks in advance for the help.

TL

If you are building the subsequent query in code, insert this query into the
IN clause as a sub query

IN ( "SELECT [Component] " & _
"FROM cm_dbo_bomstructure " & _
"WHERE [ParentPart] = """ & s & """;")



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is also the answer I'd give, but I'd suggest that the OP create a
stored QueryDef and use parameters, if required, or a reference to the
main query. IOW, this sub-query may not need to be dynamically
constructed if its WHERE clause is constructed like one of these:

WHERE ParentPart = MainQuery.PartNbr

or

PARAMETERS thisParameter Text(25);
...
WHERE ParentPart = thisParameter

Stored queries run faster (pre-compiled) and are easier to maintain
since you won't have to search through code to find them, and, you don't
have to worry about properly formatting the SQL string in VBA.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd9uZIechKqOuFEgEQI37gCfdOhsmlP1oOQrK63Fq2BR+zo7XdIAnR+I
T212KpWwNZzR+qQ4I1IPvaUE
=XjMg
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Re: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: strSQL using Like "*"
    ... so you need to craft the WHERE clause so that it does not compare to a field at all. ... Switch the query to SQL View, and edit the WHERE clause so it looks like this: ... A much more efficient solution is to create the filter string dynamically, in code, from the boxes where the user entered a value. ... to use a strSQL for the recordset and what I am querying on is based on 5 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Build an IN clause from SELECT records
    ... lyle fairfield wrote: ... string would be used for an IN clause in a subsequent SQL query. ... How do I return the results of this query to a string with each record ...
    (comp.databases.ms-access)
  • Re: issue with runing Select query with condition using code
    ... Build the whole query statement, not just the WHERE clause, and assign it like this: ... Dim strSql As String ... > Dim strsql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: strSQL using Like "*"
    ... this can be odd the first time you see it. ... Ultimately a WHERE clause is something that evaluates to True or False If it's true, the record gets included; ... Switch the query to SQL View, and edit the WHERE clause so it looks like ... A much more efficient solution is to create the filter string dynamically, ...
    (microsoft.public.access.modulesdaovba)