Another SQL problem (1 Viewer)

[John Sh]

In the code below, I am using an alias for the table name, Thank you Plog.
That all works fine but I need to include the table name in the file "NoMatchTemp" using an expression.
All my attempts, so far, have resulted in the literal expression being transfered, or [A], as in the code I get a dialog asking for the name of A.
Enclose it in inverted commas and I get "A" or & sTable & .
So, how do I use either the alias or the variable as input to the "as exp" statement.
I have also tried creating a second variable holding the table name with the same result.

sQry = "INSERT INTO NoMatchTemp (Family, Genus, Corrected_Family, Taxon_Family, TableName) " & " " & _
                            "SELECT A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, [A] AS exp" & " " & _
                            "FROM ([" & sTable & "] AS A LEFT JOIN Bad_Family ON A.Family = Bad_Family.Family) LEFT JOIN Taxon ON A.GENUS = Taxon.genus" & " " & _
                            "GROUP BY A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, Bad_Family.Family" & " " & _
                            "HAVING Bad_Family.Family = A.Family" & " " & _
                            "ORDER BY A.Family, A.Genus"

 

[The_Doc_Man]

Since you declare A to be an alias of whatever is variable sTable in your FROM clause, change this line:

"SELECT A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, [A] AS exp" & " " & _

to

"SELECT A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, '" & sTable & "' AS exp" & " " & _

Because you are inserting a literal string, you have to quote it, but you can do so with the single-quote mark. Therefore that sequence is actually "Taxon.family, <single-quote><double-quote> & sTable & <double-quote><single-quote> AS ..."

 

[John Sh]

Since you declare A to be an alias of whatever is variable sTable in your FROM clause, change this line:

"SELECT A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, [A] AS exp" & " " & _

to

"SELECT A.Family, A.Genus, Bad_Family.Corrected_Family, Taxon.family, '" & sTable & "' AS exp" & " " & _

Because you are inserting a literal string, you have to quote it, but you can do so with the single-quote mark. Therefore that sequence is actually "Taxon.family, <single-quote><double-quote> & sTable & <double-quote><single-quote> AS ..."

Thanks Docman.
I always get tangled up with the single / double quote thing.