Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup in two tables (2003)

    In the following code:-
    Dim varIWO, varDesc As Variant
    Dim Cancel As Integer
    varIWO = DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]")
    varDesc = DLookup("[Descline1]", "[Stock1]", "[Code] = [Part No]")


    If (Not IsNull(varIWO)) Then
    Me.ALLOCATED_X_RAY_No_S = varIWO
    Me.DESCRIPTION = varDesc


    ALLOCATED_X_RAY_No_s_BeforeUpdate Cancel
    End If

    the data, is retrieved from tblXrayImport. How would I be able to also check tblXayImportAli, for the same information? As, if the imformation is not in the first table, it will be in the other.

    Regards,

    Rob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup in two tables (2003)

    Something like this?

    varIWO = DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]")
    If IsNull(varIWO) Then
    varIWO = DLookup("[Xray No]", "[tblXrayImportAli]", "[BatchNo] = [Batch No]")
    End If

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup in two tables (2003)

    Thanks Hans. Brilliant.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup in two tables (2003)

    Another option is

    varIWO = Nz(DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]"), _
    DLookup("[Xray No]", "[tblXrayImportAli]", "[BatchNo] = [Batch No]"))

Posting Permissions

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