sql syntax

[sladd]

I need to run the same query(s) on multiple tables. In logic:
table=1
while table <= 3
UPDATE table SET Ledger.YTD = 150;
table=table+1
loop

Hence this statement would be the same as running:
SELECT 1.LOCN INTO testsql
SELECT 2.LOCN INTO testsql
SELECT 3.LOCN INTO testsql
Where 1,2,3 represent table names.

Any assitance is greatly appreciated.

 

[SBendBuckeye]

If the table field names are the same and only the table names change you could run them out of a function where you passed the table name in as a parameter like so:

Public Sub RunQuery(Template As String, TableName As String)
Dim strSQL As String
strSQL = Template 'maybe Select * From tblName&quot;
strSQL = Replace(strSQL, &quot;tblName&quot;, TableName)
DoCmd.RunSQL strSQL
End Sub

Public Function GetSQL(TableName As String) As String
Dim strSQL As String
strSQL = &quot;Select * From &quot; & TableName & &quot; Where etc&quot;
DoCmd.RunSQL strSQL
End Function

DoCmd.RunSQL will only work for update queries which it looks like you have. Hope this helps you get started.

Good Luck!

PS. If you have a public function defined in a standard module, you can reference that function in your actual query

Select MyFormatting(tbl1.Name) As MyName From tbl1 etc

In the above, MyFormatting would be a public function