Partial Search

[DatabaseTash]

Hi all!

I have the following code which is working on a multisearch form. I would like to make the Streets_Included a partial match rather than an exact match. I have tried including wild cards, but am obviously doing it wrong as I can't get it to work.

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Portion)=Forms!frmMultiSearchTool!Portion))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Section)=Forms!frmMultiSearchTool!Section))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Suburban_Section)=Forms!frmMultiSearchTool!Suburban_Section))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Streets_Included)=Forms!frmMultiSearchTool!Streets_Included))
ORDER BY tblCadastralPlansRegister.Parish, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Streets_Included;

 

[theDBguy]

Hi. For partial searches, you could using wildcard characters and the like operator. For example,

WHERE FieldName Like "*" & Forms!FormName.TextboxName & "*"

 

[DatabaseTash]

Thanks for the reply theDBguy

Where do I put the WHERE? Does it have to go at the start? Or can it some how be included in the 3rd last line?
This is what I tried, but am obviously way off, because it didn't work.

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Portion)=Forms!frmMultiSearchTool!Portion))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Section)=Forms!frmMultiSearchTool!Section))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Suburban_Section)=Forms!frmMultiSearchTool!Suburban_Section))
Or (((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And ((tblCadastralPlansRegister.Streets_Included)=Forms!frmMultiSearchTool!Streets_Included))
ORDER BY tblCadastralPlansRegister.Parish, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Streets_Included;

 

[The_Doc_Man]

What you tried should give you an error.

WHERE Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" X ((tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish)

At the position of the red X you have built a WHERE sub-clause for Streets_Included LIKE *something* - but what follows that X is another parenthetical expression with no logical, mathematical, or concatenation operator standing in place of that X. In YOUR example, the red X is blank. But it can't be because you have two operands with nothing between them. You SHOULD get a syntax error, perhaps "missing operator" when you try that SQL. Though if there is an earlier error I didn't see it.

Side note: In your SQL, you have a single FROM element, tblCadastralPlansRegister. You do not need to qualify the [Parish] field if it comes from the sole table in that SQL. Now you still need to qualify the Forms!frmMultiSearchTool cases, but you can do less typing for the table-resident items. Now if you had a JOIN of some kind or otherwise included multiple tables, you WOULD need to qualify things - but not for a single-table case.

 

[DatabaseTash]

Thank you for that The_Doc_Man!

That seemed to work well for the partial search in Streets_Included, but I broke something else.

How would I tweek that so that it only shows results that always include that Parish. How this stands now it is showing results for one or the other. So for example it needs to show one of these pairs:
Parish and Portion
Parish and Section
Parish and Surburban_Section
Parish and Streets_Included

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*"
Or tblCadastralPlansRegister.Portion=Forms!frmMultiSearchTool!Portion
Or tblCadastralPlansRegister.Section=Forms!frmMultiSearchTool!Section
Or tblCadastralPlansRegister.Suburban_Section=Forms!frmMultiSearchTool!Suburban_Section
Or tblCadastralPlansRegister.Streets_Included=Forms!frmMultiSearchTool!Streets_Included
ORDER BY tblCadastralPlansRegister.Parish, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Streets_Included;

Hope that makes sense.

 

[The_Doc_Man]

Let me try this in English:

You want to always match Parish and then to match one or more of Portion, Section, Suburban_Section, or Streets_Included. For the Streets_Included case, you are looking at a "LIKE" match. For the others you are looking at an exact match.

But to clarify, what happens if you match Parish, Portion, and Section at the same time. Does a "matched too many" case get disqualified or accepted?

SELECT *
FROM tblCadastralPlansRegister
WHERE 
( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) OR
( Portion=Forms!frmMultiSearchTool!Portion ) OR
( Section=Forms!frmMultiSearchTool!Section ) OR
( Suburban_Section=Forms!frmMultiSearchTool!Suburban_Section ) OR
( Streets_Included=Forms!frmMultiSearchTool!Streets_Included )
ORDER BY Parish, Portion, Section, Suburban_Section, Streets_Included ;

What you just showed us DOESN'T reference PARISH in the WHERE clause and DOES reference STREETS_INCLUDED twice - once with a LIKE and once with an "exact" match.

I'm going to take a wild-eyed guess on this and suggest that the WHERE clause might need to be more like this:

WHERE 
( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) OR
( Portion=Forms!frmMultiSearchTool!Portion ) OR
( Section=Forms!frmMultiSearchTool!Section ) OR
( Suburban_Section=Forms!frmMultiSearchTool!Suburban_Section ) ) AND
( Parish = Forms!frmMultiSearchTool!Parish )
[/CODE}

Carefully go over my placement of parentheses and the operators involved to see why this might give you closer to what you want.  But I'm shooting from the hip and making assumptions so... no guarantees.

 

[arnelgp]

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE
(tblCadastralPlansRegister.Parish)=Forms!frmMultiSearchTool!Parish) And
(tblCadastralPlansRegister.Portion=Forms!frmMultiSearchTool!Portion
Or tblCadastralPlansRegister.Section=Forms!frmMultiSearchTool!Section
Or tblCadastralPlansRegister.Suburban_Section=Forms!frmMultiSearchTool!Suburban_Section
Or Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*")
ORDER BY tblCadastralPlansRegister.Parish, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Streets_Included;

 

[DatabaseTash]

Thanks guys for the different ideas. I have tried this code and it seems to work well for the Streets_Included. However when I search by Parish and Portion it returns incorrect results. For example If I search for the Parish of Barolin and Portion 4, it returns Barolin records for any of the portions. The only thing I can see that they have in common is that the Streets_included field is populated for each record.

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) OR
( Portion=Forms!frmMultiSearchTool!Portion ) OR
( Section=Forms!frmMultiSearchTool!Section ) OR
( Suburban_Section=Forms!frmMultiSearchTool!Suburban_Section ) ) AND
( Parish = Forms!frmMultiSearchTool!Parish )
ORDER BY tblCadastralPlansRegister.Parish, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Streets_Included;

 

[The_Doc_Man]

I think what is happening ("returns Barolin records for any of the portions") is because if Streets_Included is empty, then you have a search that allows "Streets_Included LIKE **" - which will match bloody well anything. I would bet that ANY search for other than a specific street name will return more than you bargained for.

Which means that you need to guard against the overreach of the only "LIKE" in the mix.

Change

WHERE ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) OR ...

to

WHERE ( ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) AND ( Forms!frmMultiSearchTool.Streets_Included <> "" ) ) OR ...

That should help.

 

[DatabaseTash]

Thank you The_Doc_Man that seemed to do the trick!
While I'm at it, I decided that I should change the other search fields to partial searches also. As they aren't really displaying true results. This is the code I have now. I don't think I have the Parish line right though. No matter how I try, I still get other parishes with the one I have searched for. For example if I search for Parish of Barolin and Portion 4, I get results for Barolin and also other parishes with that Portion 4.
If I ask for an exact match it probably won't return the Parish records that include 2 parishes. An example of this is Barolin/Bundaberg or Kalkie/Takalvan.

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE ( ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) AND ( Forms!frmMultiSearchTool.Streets_Included <> "" ) ) OR
( ( Portion Like "*" & Forms!frmMultiSearchTool.Portion & "*" ) AND ( Forms!frmMultiSearchTool.Portion <> "" ) ) OR
( ( Section Like "*" & Forms!frmMultiSearchTool.Section & "*" ) AND ( Forms!frmMultiSearchTool.Section <> "" ) ) OR
( ( Suburban_Section Like "*" & Forms!frmMultiSearchTool.Suburban_Section & "*" ) AND ( Forms!frmMultiSearchTool.Suburban_Section <> "" ) ) AND
( Parish = Forms!frmMultiSearchTool!Parish ) )
ORDER BY tblCadastralPlansRegister.Streets_Included, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Parish;

 

[The_Doc_Man]

For example if I search for Parish of Barolin and Portion 4, I get results for Barolin and also other parishes with that Portion 4.

After a while it gets hard to be sure, but that behavior makes me think you have parenthetical expressions improperly bounded so that your AND and OR logic gets a bit messier than it needs to be. Re-check your parenthetical grouping.

 

[DatabaseTash]

Thanks for the hint!! I worked it out and it is doing everything I need it to now.

Thank you so much for your help! You are worth your weight in gold!!

This is the code I ended up with.

SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE ( ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) AND ( Forms!frmMultiSearchTool.Streets_Included <> "" ) ) OR
( ( Portion Like "*" & Forms!frmMultiSearchTool.Portion & "*" ) AND ( Forms!frmMultiSearchTool.Portion <> "" ) ) OR
( ( Section Like "*" & Forms!frmMultiSearchTool.Section & "*" ) AND ( Forms!frmMultiSearchTool.Section <> "" ) ) OR
( ( Suburban_Section Like "*" & Forms!frmMultiSearchTool.Suburban_Section & "*" ) AND ( Forms!frmMultiSearchTool.Suburban_Section <> "" ) ) ) AND
( Parish Like "*" & Forms!frmMultiSearchTool.Parish & "*" ) AND ( Forms!frmMultiSearchTool.Parish <> "" )
ORDER BY tblCadastralPlansRegister.Streets_Included, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Parish;

 

[arnelgp]

my comment:

all code with Forms!frmMultiSearchTool.textbox <> "" will never happen.
if the textbox have "no value", then it is Null.

so you cannot compare a Null to a zls (zero length string).

you can just remove the expression and replace with:

Nz(Forms!frmMultiSearchTool.textbox, "@!@!@!@!")

 

[DatabaseTash]

my comment:

all code with Forms!frmMultiSearchTool.textbox <> "" will never happen.
if the textbox have "no value", then it is Null.

so you cannot compare a Null to a zls (zero length string).

you can just remove the expression and replace with:

Nz(Forms!frmMultiSearchTool.textbox, "@!@!@!@!")

@arnelgp thanks for the comment. Sorry for the delay in replying. A Christmas break thrown in the middle means I haven't had a chance to get back to this.

The code I have mentioned in #12 seems to work well from what I have tested. Is your concern that they will only search by one field and therefore not return a result?
I'm not sure I have understood your code suggestion correctly. This is what I tried. When I tested it, the result returned was for everything relating to the parish rather than the portions relating to the parish.

SELECT tblCadastralPlansRegister.*

FROM tblCadastralPlansRegister

WHERE ( ( ( Streets_Included Like "*" & Forms!frmMultiSearchTool.Streets_Included & "*" ) AND NZ(Forms!frmMultiSearchTool.Streets_Included, "@!@!@!@!" ) ) OR

( ( Portion Like "*" & Forms!frmMultiSearchTool.Portion & "*" ) AND NZ(Forms!frmMultiSearchTool.Portion, "@!@!@!@!" ) ) OR

( ( Section Like "*" & Forms!frmMultiSearchTool.Section & "*" ) AND NZ(Forms!frmMultiSearchTool.Section, "@!@!@!@!") ) OR

( ( Suburban_Section Like "*" & Forms!frmMultiSearchTool.Suburban_Section & "*" ) AND NZ(Forms!frmMultiSearchTool.Suburban_Section, "@!@!@!@!") ) ) AND

( Parish Like "*" & Forms!frmMultiSearchTool.Parish & "*" ) AND NZ(Forms!frmMultiSearchTool.Parish, "@!@!@!@!")

ORDER BY tblCadastralPlansRegister.Streets_Included, tblCadastralPlansRegister.Portion, tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section, tblCadastralPlansRegister.Parish;