Unanswered: SQL comparison between two takes HELP Please
Hail to all,
I am in need of your assistance. I am needing to run a query to search for the 'DOC NBR' on an update table which changes daily and find the matching 'DOC NBR' on the primary table and then display 4 fields of the Update table if the 'STATUS' field on the primary table does not equal 'clos'.
What I am wanting to do is create a form with a text box on it and a Run Query button.
They will type in the name of the updated table in the text box.
And then click Run Query. It will then dispaly the 4 fields that I need from the update table.
the problem that I'm having is that I do not know how to enter a variable as the table I'm figuring that I need to do this in VBScript because in the query option I have to link the 'DOC NBR' fields on the tables and then change the tables names and can't enter a variable there.
I've converted my query to sql statement in access so you can see it. i've entered VARIABLE where the table is the update table that the name will pull from the text box.
If anyone can help it would be greatly appreciated. i hope my question isn't to complicated and that someone actually reads through it i know it's long :-p
Thank you all.
here is the sql script that the query created.
SELECT [VARIABLE].[DOC NBR], [VARIABLE].[CASE CLM NBR], [HS FSS ADHOC].STATUS, [VARIABLE].TRANSACTION FROM [HS FSS ADHOC] INNER JOIN [VARIABLE] ON [HS FSS ADHOC].[DOC NBR] = [VARIABLE].[DOC NBR] WHERE ((([HS FSS ADHOC].STATUS)<>"clos"));
You could do this in VBA. Have the value that they enter into the text box go to a variable. Then when they click the Run Query button build your sql string pluging in the variable at the table name. Then execute the sql string to a recordset and have it displayed to the form. Or you can set the source of the object you are populating with the sql string.
Yeah this is exactly what I want to do. I just don't know how to structure the code. I posted the sql statement that access uses in the query. I bought a book that I'm starting to read on VBA for access but would appreciate any assistance anyone would offer.
Assuming that the 4 fields you want displayed will go into textboxes:
Create the textboxes with the fields you want them to be. Then in the code behind the run query button - try this (of course change the query to your query and the name of the textbox that they will be entering the table name):
Dim tb1 As textbox
Dim sqlstring As String
Set tb1 = Me.textbox
sqlstring = "Select * from " & tb1
Me.RecordSource = sqlstring