Syntax problem using string for table name in SQL statement

[mabrynda]

Good morning everybody.
I have the following problem. I would like to use a table name as variable (readed directly from a form) in a SELECT sql statement. I can't find the proper syntax to make it work and I get always a syntax error for this particular statement. It works perfectly when I use a variable for a field name, but stops working when I use a variable for table name. Any help will be more than appreciated.

Here is the working code where the variable is a field name:

DoCmd.Run SQL "SELECT FMFactorsCodes.Factor_RiskPremium, Count(FMFactorsCodes.Factor_Code) AS CountOfFactor_Code INTO tblFactorsCodes1 " & _
" FROM FMFactorsCodes WHERE [FMFactorsCodes].[" & Me.FactListSelector1 & "]=1 GROUP BY FMFactorsCodes.Factor_RiskPremium", False

And here is the NON-working code that I'm trying to run where the variable is a table name:

DoCmd.Run SQL "SELECT [" & Me.FactListSelector1 & "].[Factor_RiskPremium], Count([" & Me.FactListSelector1 & "].Factor_Code) AS CountOfFactor_Code INTO tblFactorsCodes1 " & _
" FROM [" & Me.FactListSelector1 & "] WHERE [" & Me.FactListSelector1 & "].[Factor_Name] IS NOT Null GROUP BY [" & Me.FactListSelector1 & "].Factor_RiskPremium", False

Thank you in advance.

[mabrynda]

I think I found myself a straightforward way to do it, still keeping table name as variable:

DoCmd.RunSQL "SELECT Count(Factor_Code) AS CountOfFactor_Code, Factor_RiskPremium INTO tblFactorsCodes1 " & _
"FROM " & Me.FactListSelector1 & " GROUP BY Factor_RiskPremium", False

M.

[NeoPa]

I've checked both your working and your non-working SQL. They're both perfect as far as the usage of the variable as the table name goes.

It's always a good idea when selecting from a single record source (as you are in this case) to omit it from field references. It's not necessary as with a single record source there is no ambiguity.

This brings us to the fundamental difference between the two versions. The problem one has a WHERE clause which is missing from the version which works. The format of the WHERE clause is perfect, so I can only assume there is a problem with [Factor_Name] field, at least in the table you were working with when you had the problem.

Would it be possible to learn what problem was reported when you attempted the code which didn't work? I'm not sure why this wasn't part of the original question. Perhaps you overlooked it.

[NeoPa]

Actually, forget that question. Your working version uses DoCmd.RunSQL, whereas the problem version tries to use DoCmd.Run SQL. That would certainly explain the problem. I thought the SQL looked good.