Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2015
    Posts
    22

    Answered: Dlookup issue with multiple tables

    Hi All,

    I have two tables: TABLE 1 & TABLE 2.

    TABLE 1 has past data (until yesterday) and TABLE 2 has today's data.

    When the day is done, the data from the TABLE 2 are moved to the TABLE 1 (which is the master table) and the TABLE 2 (which is the temporary one) gets empty.

    I have also an import form with which I pick and move only the data I want to a new table (TABLE 3 - that contains only my picks) based on the criteria (code and date) that I enter in the import form.

    So, through the form which has 3 fields, exactly like those of the tables (FIELD 1 = a code, FIELD 2 = a date & FIELD 3 = a name) I choose the desired code and date in order to make the registration of all the three fields to the third table.

    What I would like to do though is, when I enter the code into the CODE & DATE Fields, the NAME Field of the FORM to appear the name that is related with the other two fields and is already registered in the tables 1 or 2 (it depends on the date that I make the choice).

    Normally with a DLookup function on the Control Source I could appear the desired name, but the problem is that the data can be stored either in the TABLE 1 or in the TABLE 2 (based on the date of the registration) and I don't know how to ask access to look in both tables.

    Any suggestions on how I could solve that issue? Could it be a possibility to write the DLOOKUP function in a way that it could check where the entry is already stored (Table 1 or 2) and then appear the data?

    Thank you in advance!

  2. Best Answer
    Posted by ranman256

    "create a UNION query, using both tables, THEN run a dlookup against that.
    qnUnion =
    select * from table1
    union
    select * from table2


    Dlookup("[field]","qnUnion","[date]=#" & txtDate & "#")"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    create a UNION query, using both tables, THEN run a dlookup against that.
    qnUnion =
    select * from table1
    union
    select * from table2


    Dlookup("[field]","qnUnion","[date]=#" & txtDate & "#")

  4. #3
    Join Date
    Oct 2015
    Posts
    22
    Thanks!
    I should have thought of it by myself!

Posting Permissions

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