none
DLookup Concatenation

    Question

  • I have an Address table that has

    First Name
    Last Name

    as some of the columns in the table.

    I need to be able to lookup a column in the Address table called MedicalSpeciality therefore I am trying to use DLookUp but I am having problems with the criteria portion of the statement.  Basically, I need to lookup the value in the Address table based on a concatenation of the First Name and Last Name fields in the table.  For example:

    IN the address table

    First Name = Dr. Peter
    Last Name = Mollica, Sr.

    The field that I use to lookup the MedicalSpeciaity value in the Address table is on a form and is concatenated together:

    Dr. Peter Mollica, Sr.

    I hope this make sense, below is the DLookUp statement that is not working:

        Me.MedicalSpeciality = DLookup("MedicalSpeciality", "Addresses", _
            "[First Name] & ' ' & [Last Name]= " & Me.AP_With_Whom)

     I get an error message saying there is a syntax error in the statuement.

    Any help would be greatly appreciated!


    PietroA
    Wednesday, May 25, 2011 10:29 PM

Answers

  • Since you're dealing with text, you need quotes:

    Me.MedicalSpeciality = DLookup("MedicalSpeciality", "Addresses", _
            "[First Name] & ' ' & [Last Name]= """ & Me.AP_With_Whom & """")

    or

    Me.MedicalSpeciality = DLookup("MedicalSpeciality", "Addresses", _
            "[First Name] & ' ' & [Last Name]= '" & Me.AP_With_Whom & "'")

    (The second one will fail on names with apostrophes in them)


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by PietroA Wednesday, May 25, 2011 11:31 PM
    Wednesday, May 25, 2011 11:14 PM

All replies