Variable table name and Variable Criteria for Append Query 1

[TeddyTerrier]

I need to append data from one table (Transfer) to 1500 different tables without keying in the table names or the criteria for the query to use. I have been able to get the data to go to the correct table, but the I am having to type in the criteria for the filter. Here is what I have done.

Transfer is the name of the table that the original data is coming from.

1. Declared a public variable: Public gTableName As Variant

2. Assigned a value to gTableName: gTableName =[tBridgeNo] (tBridgeNo is a field name in the Transfer table)

3. Run a SQL: DoCmd.RunSQL “Insert Into" &
gTableName & "(Date_of_Survey) Select Transfer.Inspdate From Transfer Where (((Transfer.tBridgeNo) = gTableName))",-1

When I run this code it appends the data to the correct table, but I still have to key in the criteria for the filter. I don't understand why the variable table name is working, but not the varible filter when it is the same as the table name.

Do you any of you see what I am doing wrong?

Thank you,

TeddyTerrier

 

[Golom]

Because of your use of quotes ...
Try

DoCmd.RunSQL "Insert Into " &
gTableName & "(Date_of_Survey) Select Transfer.Inspdate From Transfer Where (((Transfer.tBridgeNo) = '" & gTableName & "))",-1

 

[TeddyTerrier]

Thank you Golom. I tried your code and I got an error that indicated that I needed another '. I added one at the end of the code : ..... gTableName & "'))",-1
and it worked great. Thank you so much.

TeddyTerrier