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

    Answered: Dlook function with criteria

    =DLookUp("[DriverName]","qryDrivers","[DriverID]=" & [Forms]![frmDriver]![BusDriverID])
    Please can someone explain what ( " &) does mean in the abov function
    Why there is (" &)?? What if I remove these two things from the above formula??

  2. Best Answer
    Posted by healdem

    "you will make life easier for yourself by getting to grips with a naming convention

    ..when I see a column ending with or including ID I expect that column to be numeric, and almost invariably an integer of some form
    ..I dont' agree with the Hungarian notation of Simonyi et all especially within table and column names a good choice of naming a column in a tbale makes it clear to you as the developer and others following on from you waht the data may well be. this is especially helpful when asking questions on a forum where ou don't provide the original table design.
    ..so to me to have a control in a form called busdriverid which actually contains the drivers name seems counter intuitive and created scaop for confusion. is it a problem, certainly not, but its soemthign worth consodering moving on."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    the dlookup (or query) must have fields and table inside quotes so the program sees the literal meaning.
    The ampersand is a concatenation for string , (string addition) "con" & "cat" = "concat"
    the & is used to join the item on the form object, [Forms]![frmDriver]![BusDriverID], which must be resolved into its value
    [Forms]![frmDriver]![BusDriverID] will read as 2541
    thus creating the readable string as: [DriverID]=2541

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    & in VBA (Visual Basic for Applications) is the string concatenation character

    so
    mystring = "abcdef" & " : " & "ghijkl"
    means mystring will now have "abcdef : ghijkl" in it.

    this is a way of building a value in a variable from different elements.

    in this specific case
    Code:
    =DLookUp("[DriverName]","qryDrivers","[DriverID]=" & [Forms]![frmDriver]![BusDriverID])
    lets assume the control BusDriverID had 1234 in it., then
    Code:
    "[DriverID]=" & [Forms]![frmDriver]![BusDriverID]
    will be evaluated as
    [DriverID]=1234

    if you omitted the string delimiter, the ", AND the concatenation, the &, then
    ..you would need an additional closing " after the end of
    Code:
    "[DriverID]=[Forms]![frmDriver]![BusDriverID]"
    but that code would then resolve to
    Code:
    "[DriverID]=[Forms]![frmDriver]![BusDriverID]"
    instead of adding the value of the control [frmDriver]![BusDriverID] to the driver id you have added the actual (literal) text.

    think of it as a way of dropping in and out of visual basic code when manipulating variables / controls. all string literals must be delimited.

    username = "user: alipezu"
    would be the same as
    username = "user: " & myusername
    but not the same as
    username = "user: myusername"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    Nov 2015
    Posts
    23
    Thanks alot for the explanation !!

  6. #5
    Join Date
    Nov 2015
    Posts
    23
    Ok here is one more question:
    =DLookUp("[DriverName]","qryDrivers","[DriverID]=" & [Forms]![frmDriver]![BusDriverID])
    this code gives me DriverName if I type DriverID in BusDriverID
    Now I want a DriverID in result if I type DriverName
    Since it is text not a numeric number.. I just added single quotes like this
    =DLookUp("[DriverID]","qryDrivers","[DriverName]=" & '[Forms]![frmDriver]![BusDriverID]')
    not working Please help!!
    Last edited by alipezu; 12-18-15 at 03:47.

  7. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You have rightly included string delimiters (apostrophise) in the criteria clause for the text/string variable type, but unfortunately you have not included them in the string!
    So this
    Code:
    =DLookUp("[DriverID]","qryDrivers","[DriverName]=" & '[Forms]![frmDriver]![BusDriverID]')
    should look like this
    Code:
    =DLookUp("[DriverID]","qryDrivers","[DriverName]='" & [Forms]![frmDriver]![BusDriverID] & "'")
    HTH

    MTB

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you will make life easier for yourself by getting to grips with a naming convention

    ..when I see a column ending with or including ID I expect that column to be numeric, and almost invariably an integer of some form
    ..I dont' agree with the Hungarian notation of Simonyi et all especially within table and column names a good choice of naming a column in a tbale makes it clear to you as the developer and others following on from you waht the data may well be. this is especially helpful when asking questions on a forum where ou don't provide the original table design.
    ..so to me to have a control in a form called busdriverid which actually contains the drivers name seems counter intuitive and created scaop for confusion. is it a problem, certainly not, but its soemthign worth consodering moving on.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #8
    Join Date
    Nov 2015
    Posts
    23
    yes healdem, you are right,
    It is BusDriverName rather than BusDriverID...

  10. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by alipezu View Post
    yes healdem, you are right,
    It is BusDriverName rather than BusDriverID...
    is it?
    =DLookUp("[DriverID]","qryDrivers","[DriverName]='" & [Forms]![frmDriver]![BusDriverID] & "'")
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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