query on different tables

[Parintas Themis STE Kardias]

Hi i have a query, and i use the fields from a table( TABLE1), i have make
some criteria on the query
How can i use the same query (programmatically, via code) with another table
(TABLE2) wich table has th same fields.
Thanks

[Salad]

Parintas Themis STE Kardias wrote:

Hi i have a query, and i use the fields from a table( TABLE1), i have make
some criteria on the query
How can i use the same query (programmatically, via code) with another table
(TABLE2) wich table has th same fields.
Thanks

Dim qdf As QueryDef
Dim s As String
Dim sOrder As String
Dim intPos As Integer
Dim sTable1 As String
Dim sTable2 As String
Dim intLen As Integer

sTable1 = "Table1" 'name of table1
sTable2 = "Table2" 'name of Table2
intLen = Len(sTable1)

Set qdf = CurrentDb.QueryDefs("Query1")
'get the SQL statement
s = qdf.SQL

'get the order by section
intPos = InStr(s, "Order By")
If intPos > 0 Then sOrder = Mid(s, intPos)

'remove where clause
intPos = InStr(s, "Where")
If intPos > 0 Then s = Left(s, intPos - 1)

'now remove all references to Table1 and replace
'with Table2
Do While InStr(s, sTable1) > 0
intPos = InStr(s, sTable1)
s = Left(s, intPos - 1) & sTable2 & _
Mid(s, intPos + intLen)
Loop

'now create the SQL string adding the Where clause and Order by
s = s & "Where .... " & sOrder

MsgBox s

'you can make the SQL of the QDF like this
'qdf.SQL = s
Set qdf = Nothing

'or
'you can now use the string "s" as a recordsource.