By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

Syntax problem using string for table name in SQL statement

P: 24
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Run SQL "SELECT FMFactorsCodes.Factor_RiskPremium, Count(FMFactorsCodes.Factor_Code) AS CountOfFactor_Code INTO tblFactorsCodes1 " & _
  2. " 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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Run SQL "SELECT [" & Me.FactListSelector1 & "].[Factor_RiskPremium], Count([" & Me.FactListSelector1 & "].Factor_Code) AS CountOfFactor_Code INTO tblFactorsCodes1 " & _
  2. " FROM [" & Me.FactListSelector1 & "] WHERE [" & Me.FactListSelector1 & "].[Factor_Name] IS NOT Null GROUP BY [" & Me.FactListSelector1 & "].Factor_RiskPremium", False
Thank you in advance.
Mar 26 '10 #1
Share this Question
Share on Google+
3 Replies

P: 24
I think I found myself a straightforward way to do it, still keeping table name as variable:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "SELECT Count(Factor_Code) AS CountOfFactor_Code, Factor_RiskPremium INTO tblFactorsCodes1 " & _
  2. "FROM " & Me.FactListSelector1 & " GROUP BY Factor_RiskPremium", False
Mar 26 '10 #2

Expert Mod 15k+
P: 31,494
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.
Mar 26 '10 #3

Expert Mod 15k+
P: 31,494
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.
Mar 26 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.