Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    48

    Question Unanswered: Problem with DLookup

    My intent is to perform an action based on the part number [Part #] that a user clicks on in form [Parts Book (Form)]. There is a button on [Parts Book (Form)] that shows details, if any, on the [Part #] selected. If details exist they will be listed in table [Part Source] indexed by [PartNumber]. [Part Number] will be the same as [Part #]. Since I want to see if the DLookup statement works before I go any further, I wrote only that part of the code:

    Private Sub Form_Current()

    Dim Varx as Variant
    Dim strPartNumber as String

    On Error GoTo ExitError;

    strPartNumber = Forms![Parts Book (Form)]![Part #]
    VarX = DLookup("[PartNumber]", "[Part Source]", "[PartNumber] = " & strPartNumber)

    ExitError:
    Exit Sub
    End Sub

    Please note that the VarX = line is all one line in the actual code.

    strPartNumber is correct; the problem is VarX always comes up Empty whether or not there is a PartNumber in the Part Source table that is the same as Part #. I have tried using quotes like this "[PartNumber] = strPartNumber" with the same results.

    What am I doing wrong?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is partnumber a number or a string / text column?
    VarX = DLookup("[PartNumber]", "[Part Source]", "[PartNumber] = '" & strPartNumber & "'")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2009
    Posts
    48

    DLookup Problem

    It is a string as are all of the fields referenced in the code.

  4. #4
    Join Date
    Jun 2009
    Posts
    48

    Red face dlookup

    Healdem,

    I want to try the DLookup statement with your choice of quotes, but with the font used here I can't tell the difference between " ' " and ' " " or any other combination of 2 double and 1 single quote. Also, I don't understand the purpose of the last &.

    Thanks,

    Charles

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a string field then you must encapsulate the value with either " or '. in your case becuase you are encapsualting your other contnet with " I suggested using '. some other languages/database engines would have you use an escape character often the \ symbol

    Code:
    VarX = DLookup("[PartNumber]", "[Part Source]", "[PartNumber] = '" & strPartNumber & "'")
    thats how the data engine, in this case JET understands where the string to be amtched stops and starts.

    if you find it hard to read then consider using the ascii character 34 to encapsulate the text, it does make code easier to read

    Code:
    VarX = DLookup("[PartNumber]", "[Part Source]", "[PartNumber] = " & chr$(34) & strPartNumber & chr$(34))
    its doing the same job but in my books its easier for humans to read.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2009
    Posts
    48

    Smile DLookup

    Healdem,

    That works! Thanks for your patience and your help.

    Charles

Posting Permissions

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