Can I create a search criteria with data from a lookup?

[Danelloc]

I have a table with separate fields for First and Last names. I have a query which combines these fields as "Last Name, First Name". I have another table which uses this query as a Lookup for the name.



When I create a query for this second table, it displays the names in a single field with "Last Name, First Name" as it should.

I tried to use an [Enter Name] search criteria for the query, but when I try to enter a name (example: Public, Joe), I get an error message "Misspelled or too complex, etc"

Is the problem that I'm basing the name search on a lookup field?

If so, is there a way around it?

[CJ_London]

Should work so sounds like what you are actually doing is not what you think you are doing. Provide the sql to both your queries

[Welshgasman]

Works for me?

SELECT Crew.Surname, Crew.Initials, [surname] & " " & [initials] AS Expr1
FROM Crew
WHERE ((([surname] & " " & [initials]) Like "*" & [Enter Name] & "*"));