Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015
    Posts
    23

    Answered: two fields with the same name in query, how can I use my Dlookup function for that??

    I have a text boxes on my Form1 and I am using Dlookup to retrieve Driver Name from "qryBusesDrivers" but the problem is there are two [ID] fields in the query, one is a Bus ID and the other is Driver ID.. my Dlookup function is as =DLookUp("[DriverNameAr]","qryBusesDrivers","[ID]=" & [Forms]![frmAddNewTrip]![Bus_Driver_ID])

    How Microsoft Access can understand the [ID] is a Bus ID not a Driver ID? but they are in the same query


    Please help
    thanks!!

  2. Best Answer
    Posted by healdem

    "explicitly alias them

    SELECT B.ID as Bus_ID, D.ID as DriverID.... From BUS as B, Drivers as D

    Access does a heck of a lot under the covers of a form or report.

    be careful you don't confuse a control name with an actual column name. usdually Access allocates the same name as the underlying column to a conjtrol (so a control holding the column Drivers_Name will defautl to the same).

    however that may not hold in this case as you are suign dlookups... are you using these in a query or in a form/report.

    often you do not need to use a dlookup if the data is coming form a query.,.. you can JOIN tabels together

    eg say you have the follwoign struture
    table Buses
    ID
    Reg_No
    ..

    table Drivers
    ID
    First_Name
    ...

    table BusDrivers
    Bus_ID
    Driver_ID

    then in one query
    SELECT B.ID as Bus_driver, B_Reg_no, D.ID as Driver_ID, D.First_Name & " " & D.Last_Name as D.Driver from BusDrivers as BD
    Join Buses as B on BD.Bus_ID = B.ID
    Join Drivers as D on BD.Driver_ID = D.ID"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    explicitly alias them

    SELECT B.ID as Bus_ID, D.ID as DriverID.... From BUS as B, Drivers as D

    Access does a heck of a lot under the covers of a form or report.

    be careful you don't confuse a control name with an actual column name. usdually Access allocates the same name as the underlying column to a conjtrol (so a control holding the column Drivers_Name will defautl to the same).

    however that may not hold in this case as you are suign dlookups... are you using these in a query or in a form/report.

    often you do not need to use a dlookup if the data is coming form a query.,.. you can JOIN tabels together

    eg say you have the follwoign struture
    table Buses
    ID
    Reg_No
    ..

    table Drivers
    ID
    First_Name
    ...

    table BusDrivers
    Bus_ID
    Driver_ID

    then in one query
    SELECT B.ID as Bus_driver, B_Reg_no, D.ID as Driver_ID, D.First_Name & " " & D.Last_Name as D.Driver from BusDrivers as BD
    Join Buses as B on BD.Bus_ID = B.ID
    Join Drivers as D on BD.Driver_ID = D.ID
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Nov 2015
    Posts
    23
    Thank you so much mate!!
    I aliased them and it worked perfect... Hopefully, I will get further help from you.. i am very thankful to you..

Posting Permissions

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