translating a query to vba

[MilaK]

Hello,
How to translate the following query to vba so I can use it inside a module to open a recordset.

SELECT DISTINCT ConcatRelated("id","tbl_citation_symbols","symbol like '*" & [symbol] & "*'") AS Expr1, tbl_citation_symbols.symbol
FROM tbl_citation_symbols;

I have tried:

SQL = "SELECT DISTINCT ConcatRelated(tbl_citation_symbols.id,tbl_citation_symbols,tbl_citation_symbols.symbol like '*' & [symbol] & '*'') AS Expr1, tbl_citation_symbols.symbol FROM tbl_citation_symbols"

thank you,

Mila

 

[Minty]

Does the first one actually work ? It doesn't look right to me.

 

[MilaK]

Yes, the query executes fine inside the query wizard, however, i can't figure out how to write it in vba.

 

[plog]

Last edited by MilaK; Today at 08:12 AM.

Minty--you're not going insane. The original code could not have worked. He changed it.

Mila--I would take what is now the first SQL code and replace the double quotes with double double quotes and set it equal to the SQL variable:


SELECT DISTINCT ConcatRelated("id"...
SQL = "SELECT DISTINCT ConcatRelated(""id""..."

 

[MilaK]

@Minty The symbol returns the correct values but Expr1 returns Null value for some reason. Thanks for your help.

 

[Uncle Gizmo]

Minty >>> CORRECTLY SAID

Does the first one actually work ? It doesn't look right to me.

You REPLIED

Yes, the query executes fine inside the query wizard, however, i can't figure out how to write it in vba.

You should have pointed out that you had corrected the mistake of leaving off the "FROM Clause" in your SQL statement. I looked at your question earlier and I saw the from clause was missing....

 

[MilaK]

I apologize for not mentioning that I've corrected the original post.
thanks