Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    75

    Unanswered: DLookup variable field

    How can I use a variable to define the field in a DLookup function?

    For example... In a table "HtWtTbl", I want to look in the field defined by:

    If Age >= 17 And Age <= 20 Then
    HWCAT = "1" & [Gender]
    ElseIf Age >= 21 And Age <= 27 Then
    HWCAT = "2" & [Gender]
    ElseIf Age >= 28 And Age <= 39 Then
    HWCAT = "3" & [Gender]
    ElseIf Age >= 40 Then
    HWCAT = "4" & [Gender]
    End If

    (this results in the name of one of the field values: 1M, 2M, 3M, 4M, 1F, 2F, 3F, or 4F)

    Where the field HT value is equal to my form's [height] value.

    So... why doesn't the following work?

    STR1 = (DLookup(HWCAT, "HTWTTBL", "[HT] = " & Nz([Forms]![ind data pages]![Height], "0")))

    I get the error "Syntax error (missing operator) in query expression '1m'"

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I think the DLookUp requires the first parameter to also be enclosed in double quotes. There may be other probs, but I would start there.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Jan 2004
    Posts
    75

    Re: DLookup variable field

    I think I figured it out... you're right on both counts. I needed quotes AND I have other problems.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: DLookup variable field

    Originally posted by sublimsos
    I think I figured it out... you're right on both counts. I needed quotes AND I have other problems.
    Something like: DLookUp(Chr(22) & TextBox.value & chr(22), ....

  5. #5
    Join Date
    Jul 2004
    Posts
    67
    I'm having an issue with a variable in a DLookup also...what was your solution?

  6. #6
    Join Date
    Jan 2004
    Posts
    75
    I'm with a different company now and don't have a copy of the database here with me at work, but if I remember correctly...

    I tried doing a string statement using chr()... there is a great website detailing all the chr() with #s that someone else may have handy... I don't have the link anymore.

    What I do remember is that it never worked! I used a frustratingly painful series of IF statements to get what I wanted!

    The good news is... there is always another solution.

  7. #7
    Join Date
    Jan 2004
    Posts
    75
    I'm with a different company now and don't have a copy of the database here with me at work, but if I remember correctly...

    I tried doing a string statement using chr()... there is a great website detailing all the chr() with #s that someone else may have handy... I don't have the link anymore.

    What I do remember is that it never worked! I used a frustratingly painful series of IF statements to get what I wanted!

    The good news is... there is always another solution.

  8. #8
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    general format:
    DLookup("[Myfield]", "MyTable", "MyField=MyCondition")

    e.g. 1
    return height if name is 'John' (pay close attention to double and single quotes).

    DLookup("[Height]", "MyTable", "[Name]= 'John' ")

    e.g. 2
    return height for anybody. The name is now a variable. As a string it needs to be surrounded by single quotes. (pay close attention to double and single quotes.)

    DLookup("[Height]", "MyTable", "[Name]= '" & name & "'")
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  9. #9
    Join Date
    Jul 2004
    Posts
    67

    Thumbs down variable in different position

    jpshay,

    My problem is with a variable as the first argument (the field to lookup).

    DLookup(variable, "MyTable", "MyField=MyCondition")

    This doesn't seem to work though. The variable is a string type, if that helps (obviously, because it holds the field name).

    Thanks in advance,
    David

  10. #10
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    The following three items are just unrelated thoughts.

    1) I just tried sending a variable as the field name in the DLookup. It seemed to work fine.

    Function test()
    'declarations
    Dim strField As String
    'code
    strField = "Case_PatientInitials"
    MsgBox DLookup(strField, "tblCase", "Case_PatientSequenceNumber = 203")
    End Function

    2) I guess I'd suggest simplifying your code so you can see exactly what piece is incorrect. temporarily replace your code below with the simpler line that follows.
    STR1 = (DLookup(HWCAT, "HTWTTBL", "[HT] = " & Nz([Forms]![ind data pages]![Height], "0")))
    msgbox DLookup(HWCAT, "HTWTTBL", "[HT] = somevalue")


    3) 1M, 2M, 3M, 4M, 1F, 2F, 3F, or 4F are actual field names in your table "HTWTTBL"?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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