hello,
I have created a make table via a parameter query. The parameters are defined correctly and I am prompted each time to enter them. the DeptNo does work as my query is filtered based on a value entered, but when I enter the tablename parameter, the make table query always saves the table as @tablename, and not any text i enter for the @tablename like "mynewtable" or something. Here is the SQL code.
Any ideas? Ignore the complexity of the query, my main concern is being able to loop through some VBA code and continually change the tablename each time (along with the DeptNo. I think this will save me time vs creating a new sql statement each time and doing a docmd.runsql ...
Thanks.

PARAMETERS DeptNo Text ( 255 ), [@TableName] Text ( 255 );
SELECT AUMaster.Lawson, ChgMstr.DEPT, ChgMstr.[CHG CD], ChgMstr.[REVENUE DESCRIPTION], [Prior and Curr Year Revenue - Qty].Price, ChargeCodeProfile.[Count per Charge] AS Weight, [Prior and Curr Year Revenue - Qty].[2006 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2006 Projected Revenue], [Prior and Curr Year Revenue - Qty].[2007 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2007 Projected Revenue] INTO [@TableName]
FROM ((ChgMstr LEFT JOIN [Prior and Curr Year Revenue - Qty] ON ChgMstr.[CHG CD] = [Prior and Curr Year Revenue - Qty].ChargeCode) LEFT JOIN ChargeCodeProfile ON ChgMstr.[CHG CD] = ChargeCodeProfile.[CHG CD]) LEFT JOIN AUMaster ON ChgMstr.DEPT = AUMaster.Affinity
WHERE (((ChgMstr.DEPT)=[DeptNo]));

If I understand correctly, the @tablename value will change as you loop through the data? And this SQL is in a nested loop, perhaps? If that is the case, then you would need to do something like this:

Dim sTableName As String

sTableName = "YourTable"

PARAMETERS DeptNo Text ( 255 ), [" & sTableName & "] Text ( 255 ); _

This will allow you to pass the variable value into the SQL String. Don't try to pass the value in dynamically, as it looks like you are attempting. The string has a hard time holding the value if you do something like:

PARAMETERS DeptNo Text ( 255 ), [" & rs("TableName") & "] Text ( 255 ); _

For some reason, when using SQL strings, the value needs to be set in a variable in order for it to work. I think that is because Access is ANSI 89 SQL, not ANSI 92 SQL like SQL Server.

Did that work?



mmcdonal