DLookup Concatenation


  • 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!

    Wednesday, May 25, 2011 10:29 PM


All replies