Using MS Access/Access search
QUESTION: My Access database records include 2 fields Surname1 and Surname2.
How can I search the database to find those records containing a name (input at run time) occurring in either of the 2 surname fields?
ANSWER: Why do you have 2 surname fields? When you have fields with a number like that, its referred to as a Repeating group which violates normalization rules. If a record is going to be associated with multiple surnames, you should be using a child table.
Proper design will eliminate your problem.
---------- FOLLOW-UP ----------
QUESTION: I've called the fields Surname1 and Surname2 for this question. They could, for example, just as well be called Father and Mother or anything else you like. My users have been running with this database for 5 years. I'm not about to redesign it now. I'd just like them to be able to enter a value once and find matches. Thank you. Chris
Doesn't really matter what you call them, its not properly normalized. If it was this would not be a problem.
You can do this in a couple of ways. One way would be a UNION query that creates a single column from. for example:
SELECT recordID, Surname1 As Surname
UNION SELECT recordID, surname2 As Surname
Then you could Search through the Surname column
Another way would be to use an OR
SELECT RecordID, Surname1, Surname2
WHERE surname1 like Forms!formname!controlname OR surname2 Like Forms!formname!controlname;