combo box search multiple fields

[RJack23]

I have a combo box search on my main form that is set to search by [defendant1name]. The string code in the query is:

LookupName: IIf([defendant1name] Is Not Null,[defendant1name],[defendant2name])

I need the query to search by either [defendant1name] or [defendant2name]. I'm sure this is a simple fix but I'm :banghead: over here. Thanks in advance.

 

[The_Doc_Man]

A large part of your headache (see "headbang" emoji) is caused by not protecting yourself earlier in the design phase.

First and foremost, this is a nightmare for you because your nomenclature tells me your tables are most likely not normalized. With a normalized table structure, there is no need to search field #1 or field #2 for the same value.

Second, I don't know how you would be getting your data into your system, but it seems to me that if you have any input into the design of this system, you would take steps to "head this off at the pass" so that you don't have to do an IIF at all because you would stop the system from storing nulls in a field you intended to search.

This forum has an excellent SEARCH feature in the blue ribbon at the top of each forum page. Look for "normalization" to see how to structure your data in a way that makes it more searchable without the brick bruises to the forehead. You can also do a web search for this topic, but to keep from getting extraneous hits, try "database normalization" when doing the web-level search. (Otherwise you will get diplomatic normalization, formula normalization, etc.)

I'm going to ask a simple question that will highlight your problem in the proper light. What do you do with three defendants? How many defendants can a case have?

Here's the way I might approach this (and intended as the 10,000 foot overview)

Table: Cases
- CaseID (whatver you used for UNIQUE case identification)
- CaseName (smith vs. jones ... State of Louisiana vs. John Doe etc.)
- other stuff about the general concept of the case

Table: Defendants
- DefendantID (some sort of ID number, maybe even an autonumber)
- CaseID - Foreign Key that says "this person is a defendant in the indicated case"
- DefendantLastName
- DefendantFirstName
- DefendantMiddleName
- DefendantOrdinal (if it is important to know which defendant was named first)
- other defendant info

You don't say whether this is criminal or civil so I can't tell what other tables you might need.

Query: SELECT {a bunch of fields including CaseID, DefendantLastName, other names and other case info} FROM Cases JOIN Defendants ON Cases.CaseID = Defendants.CaseID ect.

Then when you do your search, search the QUERY to find a defendant and see which case (or cases) are associated with that person. If you have only one defendant, that is what is in the table for that case. If you have 20 defendants, you have 20 entries in the table for 1 case. But when you search, there is only one column and no need for an IIF to look for nulls. Because if you do this sort of thing, you don't ever HAVE nulls. If you don't have a defendant's name, you don't enter a record and then don't ever have to search it.

 

[RJack23]

Thank you very much for your reply! I'm not required to use the IIf statement. That is just the one that was put there when the database was first created. I just want to make the combo box more agile by allowing it to search both the defendant1name column and the defendant2name column. I see what you are saying about normalizing the data. Unfortunately, this database has been in use for a couple years already, and has 20,000+ records, so taking it back to the basics isn't really possible.

 

[jdraw]

Here is info and sample and code for a very versatile search y Allen Browne.

Lack of Normalization may be a bigger issue as you move forward.

Good luck

 

[Gasman]

A large part of your headache (see "headbang" emoji) is caused by not protecting yourself earlier in the design phase.

First and foremost, this is a nightmare for you because your nomenclature tells me your tables are most likely not normalized. With a normalized table structure, there is no need to search field #1 or field #2 for the same value.

Doc,

I have a question if I may.
I have a situation where a policy can be sole or joint.
To allow for that I merely added Joint fields to the client table. There will only ever be one extra client if any.

I can understand having a separate table if there was the ability to have multiple clients attached to that one client, but are you saying my structure is wrong?

 

[sneuberg]

I just want to make the combo box more agile by allowing it to search both the defendant1name column and the defendant2name column.

You could do that just by Oring the form references in the query. The WHERE clause would be like:

WHERE (((Table1.defendant1name)=[Forms]![Form1]![cboDefendant])) OR (((Table1.defendant2name)=[forms]![Form1]![cboDefendant]));

If you want to see what this looks like in the grid you can look at the qryForm1RecordSource query in the attached database. In this test database I put the defendands names to together in the combo box by using a union query for the combo box row source.

PS: I think you need to normalize the database. What if you get a case like the Chicago seven?

 

[jdraw]

Gasman,

I suggest you make a new thread specific to your issue/concern. Design considerations often make for lively discussion and often bring in several aspects and approaches.

@Steve,

That's great for the current issue, but as Doc said what happens when you have 3,4 or more defendants?
It appears this fact may have been overlooked/assumed/??? in the original development.
We really need to know the complexities of the "business" to determine feasible options for design.
eg: If there are on;y 2 defendants possible (ever), then proceed.

If it is uncertain, but possible to have multiple defendants, other approaches may be more stable.

Note: I highlight (ever), because I've seen too many---Oh that will never happen.
And I'm sure you and Doc and others have a list of stories on the things that won't ever occur, but did.

 

[sneuberg]

@Steve,

That's great for the current issue, but as Doc said what happens when you have 3,4 or more defendants?
It appears this fact may have been overlooked/assumed/??? in the original development.
We really need to know the complexities of the "business" to determine feasible options for design.
eg: If there are on;y 2 defendants possible (ever), then proceed.

If it is uncertain, but possible to have multiple defendants, other approaches may be more stable.

Note: I highlight (ever), because I've seen too many---Oh that will never happen.
And I'm sure you and Doc and others have a list of stories on the things that won't ever occur, but did.

I agree (see PS I added to my post) but I thought I'd give the OP a possible solution until he could get to what might be a lengthy endeavour.

 

[jdraw]

Steve,

Agree. I don't mean to make additional work - especially if it has been reviewed and impact(s) have been considered. I was only expanding a bit on normalization and getting all the facts identified and considered in the data model.
I think we are all on the same page on this.

 

[The_Doc_Man]

RJack,

If you cannot change the structure, can you at least add your own queries to it?

The other solution (first suggested by Steve) would be to build a searchable query something like this, and I am making up names as I go...

SELECT Defendant1 AS Defendant, 1 AS DefendantNumber, CaseID FROM CaseTable
WHERE NZ(Defendant1, "") <> ""
UNION
SELECT Defendant2 AS Defendant, 2 AS DefendantNumber, CaseID FROM CaseTable
WHERE NZ(Defendant2, "") <> "" ;

Then search this query and note that you can tell whether you took the hit from the defendant 1 or defendant 2 field by reading the DefendantNumber field coming back from the query.

However, I reiterate that if you find yourself cast in stone (can't change the design just because it has been used for so long), you are going nowhere long-term. That design will eventually become the weight around your neck that will topple you over.