I have a combo box on a logsheet form that is based on a query. The combo box query returns two fields from a Client table. The first field is a concatenated string showing the Client's Name and Case Code. (=[ClientName]&" "&[CaseCode]). The second query output field is the unique identifier for that client [ClientID].
I have the query bound so that [ClientID] is the data that gets stored when the user selects a client from the list. We have some clients with identical first, last, and middle names so I can't use the name as the unique identifier.
I use the concatenated field as the display because it makes it easier for the attorneys to enter data into their logsheets. (No one remembers the ClientID, but they remember the client's name.)
When I try to search on the calculated field using the client's last name, Access does not find any matching records, even when using "Any Part of Field". If you use the unique identifier [ClientID], as the search criteria, Access will find the record you are looking for, but again, no one keeps track of the cases by that number.
How can I get the search to work on the concatenated string data? Is it possible?
I have the query bound so that [ClientID] is the data that gets stored when the user selects a client from the list. We have some clients with identical first, last, and middle names so I can't use the name as the unique identifier.
I use the concatenated field as the display because it makes it easier for the attorneys to enter data into their logsheets. (No one remembers the ClientID, but they remember the client's name.)
When I try to search on the calculated field using the client's last name, Access does not find any matching records, even when using "Any Part of Field". If you use the unique identifier [ClientID], as the search criteria, Access will find the record you are looking for, but again, no one keeps track of the cases by that number.
How can I get the search to work on the concatenated string data? Is it possible?
