Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    61

    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.
    eg: Update050306
    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"));

    here is also a pic of the query.


    Thanks again.
    Attached Thumbnails Attached Thumbnails DatabaseQuery.jpg  

  2. #2
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    Hi.
    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.

  3. #3
    Join Date
    Apr 2005
    Posts
    61

    VBA Code

    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.

    thanks again

    Mythos

  4. #4
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    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

    I hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •