How can I use Dlookup on a table record that contains a comma?

[caddcop]

Just to reiterate, I have a query that contains a full name in LastName, FirstName format and I need (really want) to use that column as my match fields in a Dlookup function.
However, that seems to be an issue with the tool.

[boblarson]

That shouldn't be a problem. What is the syntax you've been trying to use (post the real code).

[Rhino373]

You have to use single quotes around your criteria like "FullName = 'Frost, Jack'"

Naturally, you shouldn't keep this field like such. You can use a constructed field with a concatenate later to show it Last, First to show it...... blah blah blah but that's how you do it.

[boblarson]

You have to use single quotes around your criteria like "FullName = 'Frost, Jack'"

Although I would argue for double quotes (probably with Chr(34) ) because last names can be notorious for single quotes which would throw it off as well (O'Brien, O'Neil, etc.).

Naturally, you shouldn't keep this field like such. You can use a constructed field with a concatenate later to show it Last, First to show it

I agree with that wholeheartedly. Separate fields for separate data. It is easier to pull them together than break them apart.

[caddcop]

Here is my code:

    Dim varX As Variant
    If IsNull(Me.Role) Then
        varX = DLookup("[Assignment]", "[StaffMembers_FullNames]", "[FullName] = " & Me.Text_NameField_Hidden)
    End If

where Assignment is the field that I want to grab the matching record from, [StaffMembers_FullNames] is the source query, [FullName] is the linking field and Me.Text_NameField_Hidden is a text box on my form whose control source is the field that contains the full name as explained.

However, the suggestion to enclose my string with single quotes did the trick.

varX = DLookup("[Assignment]", "[StaffMembers_FullNames]", "[FullName] = '" & Me.Text_NameField_Hidden & "'")

It's hard to tell, but there is a single quote followed by a double quote before the first & and a single quote inside of two double quotes after the second & and now the DLookup does not throw an error and, the value of varX is the value I am seeking.

[boblarson]

However, the suggestion to enclose my string with single quotes did the trick.

Again, because this is with names I would HIGHLY suggest not using single quotes and use Double Quotes like this:

varX = DLookup("[Assignment]", "[StaffMembers_FullNames]", "[FullName] = " & Chr(34) & Me.Text_NameField_Hidden & Chr(34))

[caddcop]

I've already been bitten by name fields with single and double quotes in them.
In my last problem, I ended up checking for single quotes or double quotes and having two queries to run depending upon if it found one or the other. I also know that this particular solution will fail if a name has both - something I can only hope, does not happen.

Unfortunately, I am limited by the fields in the tables as setup by a commercial product that we are using access to extend its reach and capabilities.

I was able to use your code, so far, with no problems.