John,
First a little advice:
* don't name your tables Sheet# and don't name your fields Field#. If
you're doing this for the example, fine, but your actual system should use
meaningful table and field names. I recommend you rename/restructure, then
post your question again. I have a feeling you have multiple tables with
similar data in them, and you probably shouldn't.
The base Access query model won't do what you want, which is run a query and
prompt for the table name and dynamically use it in the SQL. But you can
write a little VB code that will do it for you:
Public Sub updateQuery()
dim db as database, qItem as QueryDef
dim cSQL as string, tblName1 as string, tblName2 as string
set db = currentdb()
tblName1 = InputBox("Enter table 1 name", "")
tblName2 = InputBox("Enter table 2 name", "")
if isnull(tblName1) or isnull(tblName2) then
msgbox "Enter both table names"
exit sub
endif
cSQL = SELECT [" & tblName1 & "].ID, [" & tblName1 & "].Field6, [" &
tblName2 & "].ID, [" & tblName2 & "].Field6 "
CSQL = CSQL & "FROM [" & tblName1 & "] INNER JOIN [" & tblName2 & "] ON [" &
tblname1 & "].ID = [" & tblName2 & "].ID "
CSQL = CSQL & "WHERE [" & tblName2 & "].Field6 <> [" & tblName1 & "].Field6
OR [" & tblName2 & "].Field6 IS NULL;"
Set qItem = db.QueryDefs("query name")
qItem.sql = cSQL
qItem.Close
docmd.openQuery "query name"
End Sub
I think after you look at, and restructure, your database you'll find out
something like this is unnecessary.
"John Hardy" <jo*************@rogers.com> wrote in message
news:3f**************************@posting.google.c om...
I have the following SQL code in my databse as a querry.
SELECT Sheet1.ID, Sheet1.Field6, Sheet2.ID, Sheet2.Field6
FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID
WHERE ((([Sheet2]![Field6])<>[Sheet1]![Field6] Or ([Sheet2]![Field6])
Is Null));
Which does what I want. But I would prefer rather then hard code the
table name I would rather a dialog box pop up and ask for a table name
or browse for one. I have no idea how to do this with SQL. Any help
would be appreciated. I am not a programmer but I am writing something
I need done and taking a long time doing it, so answer as layman as
possible or with examples if you can.
Much appreciated!
Thanks
John