Access 97 - Simultaneously Search Multiple Fields 1

[DasaniDrinker]

There is probably an easy solution for this but I just can't see it. So here goes..

I have a table with following fields (among other fields):
Firstname
Middlename
Lastname
NameSuffix

<and yes... my client wants them separate>

From my Form I want to be able to search by full name. Everything I try requires me to type in a space for each field that has 'no' data.

For example, I have a person named John Will Doe.
When I search for John Will Doe, it will not find the name, unless i put a space after Doe (for the missing NameSuffix, which is blank in the table). I have tried by using a hidden field on my form which has the entire name concatenated together. I have also tried combining the names in a query first, but the same result. My FIND box is set to &quot;any part of field&quot;.

There must be an obvious solution I am overlooking. Please help if you can. Surely someone has seen this type of problem before.

Thanks,
DasaniDrinker

 

[Isadore]

Dasan. Your client is probably right. Better to separate fields as it is easy to concatenate them in a form or report.

Try the following three lines in the criteria of your Query Grid:

In the First name criteria:
Like (IIf([Forms]![frmMyForm]![FName]=-1,[Forms]![frmMyForm]![FName],&quot;*&quot)

In the Middle name criteria:
Like (IIf([Forms]![frmMyForm]![MName]=-1,[Forms]![frmMyForm]![MName],&quot;*&quot)

In the Last name criteria:
Like (IIf([Forms]![frmMyForm]![LName]=-1,[Forms]![frmMyForm]![LName],&quot;*&quot)

Keep all of these on the same line (AND and not OR)

So, on the form you'd have:

FName: John MName Null LName: Thomas

It'll bring back all John Thomas's. There's several ways to refine this (and do this), provide more info and we'll tweak this out a bit.

I don't see why its necessary to search on your Name Suffix but if you had a bunch of John Thomas's and you wanted to differentiate between them just add a fourth line:

In the suffix name criteria:
Like (IIf([Forms]![frmMyForm]![suffix]=-1,[Forms]![frmMyForm]![suffix],&quot;*&quot)

So, if in the tables you had:

FName: Bob MName: Null LName: Brown Suffix: Null

it'd return all the Bob Browns.

but if the table had (and you put in your form &quot;Mr.&quot;:

FName: Bob MName: Null LName: Brown Suffix: Mr.

it would only return all the Mr. Bob Browns.

 

[DasaniDrinker]

Thank you!

I will test this out and post back later today if time permits. If it fails I will try and give you more details.

DasaniDrinker

 

[DasaniDrinker]

Isadore,

I think we are on the right track, but i just can't seem to get it to work! I tried pointing the form to a query with all the fields (rather than pointing directly to the table) and then putting the &quot;Like&quot; statements under each field in question, but then it tells me the calculations are too complex (or similar).

I am a little confused by your statement about putting them all on the same line with the AND when just above that you said put each individual Like statement in criteria under each field.

There are currently 4000+ names in the database and I wanted them to be able to click FIND button on the form and get all the John Smiths by typing in &quot;John Smith&quot;, regardless of middle names, and/or suffixes like Jr., Sr., etc. As mentioned before, I have a hidden field on my form that concatenates the names so that THAT field can be searched. I am starting to think this was the wrong way to go about this.

I am just not getting something, I guess. I apologize for my ignorance. Is there another way we can try this? Maybe with a new form for searching and then I could possibly add a button to open the main form with child fields matching??? I know I can do that with reports, not sure about forms. Gosh, I am so confused!!!!

Thanks very much for your time.
DasaniDrinker

 

[Isadore]

Dasan. No problem. The above criteria I listed, all on the first line so an &quot;AND&quot; series of statements, basically allows a NULL to pass through, but not text. Its that simple.

Here's an idea. One one form I use the user enters in a partial string for the last name. Same they are looking for Tom Brown. In this textbox (call it &quot;Last&quot the user may only type bro, or brow, or even brown. On the afterupdate event of LAST I have the following code:

[lstMembers].RowSource = &quot;SELECT tblContacts.Contact_ID, tblContacts.ContactName FROM tblContacts WHERE (((tblContacts.Last) Like [Forms]![frmNewMonitors]![Last] & '*'));&quot;
[lstMembers].Requery

[lstMembers] is a listbox sitting next to the LAST textbox. Once populated the user can then &quot;click&quot; on the name they want to check address info, etc. to make sure its the right one. Say 4 Tom Browns show up. Well, by clicking on each one, a popup shows up that gives address, etc... so you can tell if its the right one. After that, the user then clicks on a button to proceed which reads the Contact ID from the list box.

The list box in your case would show all people with &quot;bro&quot; in their last names but would also show their first, middle, last and suffix (concatenate). In the above case I also bring the KEY field in and bind it to the listbox so I can keep track of who's who.

Perhaps a modified approach like this might work. Let me know.

 

[DasaniDrinker]

Hey THANKS! I tried a variation of your last suggestion and &quot;Boom&quot; I got it to work in a separate search form. I hope to be able to have it pull up the main form with the selected name when clicked...we'll see. I didn't really want to make this part of the main form, but I may have to.

Thanks Again!
DasaniDrinker

 

[Isadore]

Dasan, thanks for the *. Funny thing, I was working on a query today, that used a modification of the one I put up earlier, goes like this:

Like (IIf(Not ([tblReportFiles].[Path])=IsNull([Forms]![frmReports]![Find]),&quot;*&quot; & [Forms]![frmReports]![Find] & &quot;*&quot;,&quot;*&quot)

...and this one pulled me through yet another variation of this. Glad I could help.