Search on Split-Form

[Ekhart]

I have a split-form where I am attempting to set up a search. I currently have the search set up to search the SSN field, the FirstName field, and the LastName field... Unfortunately there is a limit to how long this string can be. Is there a way to set up a combo for First Name and Last Name... so if someone searches for Joe Blow it will fine the one that matches both fields, but if they only search for Blow it will search for just that name in either field? I would also like a way to search for first letter of last name and last 4 of ssn, so A1234 if possible. Current code below:

[SSN] Like "*" & Replace([Forms]![frmRecordsSearch]![SearchFieldTxt],"-","") & "*" Or [LastName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*" Or [FirstName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*"

[CJ_London]

Unfortunately there is a limit to how long this string can be.

use shorter table and field names but what you are saying doesn't make sense - a string can be some 65k characters long and in terms of individual fields in a query grid you have only one criteria per field so you are not hitting the query grid limits or around 1064 chars per field.

so if someone searches for Joe Blow

are they likely to search using 'oe low'? if not then remove the initial * - most user know what names start with anyway.

How are you applying your search? in a query as a recordsource to a form or applying a filter on a form? - what code are you using?. Need to know before looking at your requirement because your error does not make sense and needs to be resolved or at least understood otherwise could waste lots of times making suggestions which don't work due to an underlying problem.

[Ekhart]

I would say they should know the name accurately. It is currently set up as a filter on a form. It is through the builder which is why it is limiting to 255 characters I believe.

When I search foe Joe it will pull the Joes up, when I search for Blow it will pull the Blows up, but when I search for Joe Blow it pulls nothing up... just not sure how to get it to look in both fields and to try and match both fields to one of the words typed.

[CJ_London]

you need another 'or' in your filter

....or [Forms]![frmRecordsSearch]![SearchFieldTxt]=[firstname] & " " & [lastname]

however this may not be the right answer because you haven't answered my questions

How are you applying your search? in a query as a recordsource to a form or applying a filter on a form? - what code are you using?.

[Ekhart]

It is a filter on the form, using the code originally posted. An ApplyFilter with:

[SSN] Like "*" & Replace([Forms]![frmRecordsSearch]![SearchFieldTxt],"-","") & "*" Or [LastName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*" Or [FirstName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*"

[CJ_London]

using the code originally posted. An ApplyFilter

but you said Unfortunately there is a limit to how long this string can be. Are you saying this is not an issue any more? if so just add the code I provided to the end of your string.

[Bulzie]

Can you not just right click in the field on the form and use the search filters there?

[Ekhart]

I was actually able to get this to work simply by making a calculated field including the FirstName and LastName fields in to one. thanks for the above ideas as they led me there

[CJ_London]

I was actually able to get this to work simply by making a calculated field including the FirstName and LastName fields in to one

As suggesting in post #4?