Problem with Searching Criteria on a search form

Here's a question regarding my search criteria below. Apologies if
this is a studpidly simple question, but my brain has been frazzled by
this one! :)

The code below currently searches for text string as entered into the
keyword (txtSearchCriteria)

The trouble is the user can input an address to search on i.e. '100
anystreet'. The code will search just for that but if the underying
table field contains for example '100 (the castle) Anystreet' the
search will not find it'

Its no good telling the user to only put straight addresses in, as
they just do whatever they want anyway. :)

So the question is, what is the code below missing to find the
criteria thats entered. I suppose i am looking to find whatever
keywords the user enters wherever they occur in the field and they
don't have to be in any order!

'If the search box is blank, show all records
If IsNull(Me![txtMonName]) Then
sSQL = "SELECT ItemID, ItemAddress " & _
"FROM tItems " & _
"WHERE ItemAddress Is Not Null " & _
"ORDER BY ItemID"
Else 'otherwise, return the unique (DISTINCTROW) records with a
Image that matches
sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
"FROM tItems " & _
"WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
"*'" & _
"ORDER BY ItemID"
End If

Any help would be most appreciated.

Thanks in advance

Re: Problem with Searching Criteria on a search form

On Wed, 17 Oct 2007 12:16:30 -0000, redpears007@hotmail.com wrote:

First decide what you want. Then write the code for that.

It appears you want:
select ... from ... where ItemAddress like '*100*' or ItemAddress like
'*Anystreet*'

So pick apart the criteria, using the Split function, and put the
Where condition together.

-Tom.


>Here's a question regarding my search criteria below. Apologies if
>this is a studpidly simple question, but my brain has been frazzled by
>this one! :)
>
>The code below currently searches for text string as entered into the
>keyword (txtSearchCriteria)
>
>The trouble is the user can input an address to search on i.e. '100
>anystreet'. The code will search just for that but if the underying
>table field contains for example '100 (the castle) Anystreet' the
>search will not find it'
>
>Its no good telling the user to only put straight addresses in, as
>they just do whatever they want anyway. :)
>
>So the question is, what is the code below missing to find the
>criteria thats entered. I suppose i am looking to find whatever
>keywords the user enters wherever they occur in the field and they
>don't have to be in any order!
>
> 'If the search box is blank, show all records
> If IsNull(Me![txtMonName]) Then
> sSQL = "SELECT ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE ItemAddress Is Not Null " & _
> "ORDER BY ItemID"
> Else 'otherwise, return the unique (DISTINCTROW) records with a
>Image that matches
> sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
>"*'" & _
> "ORDER BY ItemID"
> End If
>
>Any help would be most appreciated.
>
>Thanks in advance

Re: Problem with Searching Criteria on a search form

On Oct 17, 7:16 am, redpears...@hotmail.com wrote:
> Here's a question regarding my search criteria below. Apologies if
> this is a studpidly simple question, but my brain has been frazzled by
> this one! :)
>
> The code below currently searches for text string as entered into the
> keyword (txtSearchCriteria)
>
> The trouble is the user can input an address to search on i.e. '100
> anystreet'. The code will search just for that but if the underying
> table field contains for example '100 (the castle) Anystreet' the
> search will not find it'
>
> Its no good telling the user to only put straight addresses in, as
> they just do whatever they want anyway. :)
>
> So the question is, what is the code below missing to find the
> criteria thats entered. I suppose i am looking to find whatever
> keywords the user enters wherever they occur in the field and they
> don't have to be in any order!
>
> 'If the search box is blank, show all records
> If IsNull(Me![txtMonName]) Then
> sSQL = "SELECT ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE ItemAddress Is Not Null " & _
> "ORDER BY ItemID"
> Else 'otherwise, return the unique (DISTINCTROW) records with a
> Image that matches
> sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
> "*'" & _
> "ORDER BY ItemID"
> End If
>
> Any help would be most appreciated.
>
> Thanks in advance

Hard to tell... is there a space between "*'" and "ORDER BY ItemID"?
There should be

Re: Problem with Searching Criteria on a search form

On 17 Oct, 14:14, OldPro wrote:
> On Oct 17, 7:16 am, redpears...@hotmail.com wrote:
>
>
>
>
>
> > Here's a question regarding my search criteria below. Apologies if
> > this is a studpidly simple question, but my brain has been frazzled by
> > this one! :)
>
> > The code below currently searches for text string as entered into the
> > keyword (txtSearchCriteria)
>
> > The trouble is the user can input an address to search on i.e. '100
> > anystreet'. The code will search just for that but if the underying
> > table field contains for example '100 (the castle) Anystreet' the
> > search will not find it'
>
> > Its no good telling the user to only put straight addresses in, as
> > they just do whatever they want anyway. :)
>
> > So the question is, what is the code below missing to find the
> > criteria thats entered. I suppose i am looking to find whatever
> > keywords the user enters wherever they occur in the field and they
> > don't have to be in any order!
>
> > 'If the search box is blank, show all records
> > If IsNull(Me![txtMonName]) Then
> > sSQL = "SELECT ItemID, ItemAddress " & _
> > "FROM tItems " & _
> > "WHERE ItemAddress Is Not Null " & _
> > "ORDER BY ItemID"
> > Else 'otherwise, return the unique (DISTINCTROW) records with a
> > record that matches
> > sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
> > "FROM tItems " & _
> > "WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
> > "*'" & _
> > "ORDER BY ItemID"
> > End If
>
> > Any help would be most appreciated.
>
> > Thanks in advance
>
> Hard to tell... is there a space between "*'" and "ORDER BY ItemID"?
> There should be- Hide quoted text -
>
> - Show quoted text -

OldPro, there is no problem with the code above, it works fine, when
finding a single text string that is!.

Put simply, we have entered search criteria of '100 Anystreet' into
the search box,

The field 'ItemAddress', in the table stores the data to be searched
on.

At data entry the address may have been entered in that table as '100
(formerly 99a) Anystreet'. Therefore our search, as it is, will not
find that record.

Therefore we need to tweak the code to be able to pick out '100' and
'anystreet' in that field. It currently will only find both if they
exactly the same as in the underlying table field.

The question is how does the search part of the code need to be
changed to do that. I have tried everything i can think of.

Re: Problem with Searching Criteria on a search form

redpears007@hotmail.com wrote in news:1192623390.885347.143730
@i38g2000prf.googlegroups.com:

> Here's a question regarding my search criteria below. Apologies if
> this is a studpidly simple question, but my brain has been frazzled by
> this one! :)
>
> The code below currently searches for text string as entered into the
> keyword (txtSearchCriteria)
>
> The trouble is the user can input an address to search on i.e. '100
> anystreet'. The code will search just for that but if the underying
> table field contains for example '100 (the castle) Anystreet' the
> search will not find it'
>
> Its no good telling the user to only put straight addresses in, as
> they just do whatever they want anyway. :)
>
> So the question is, what is the code below missing to find the
> criteria thats entered. I suppose i am looking to find whatever
> keywords the user enters wherever they occur in the field and they
> don't have to be in any order!
>
> 'If the search box is blank, show all records
> If IsNull(Me![txtMonName]) Then
> sSQL = "SELECT ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE ItemAddress Is Not Null " & _
> "ORDER BY ItemID"
> Else 'otherwise, return the unique (DISTINCTROW) records with a
> Image that matches
> sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
> "FROM tItems " & _
> "WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
> "*'" & _
> "ORDER BY ItemID"
> End If
>
> Any help would be most appreciated.
>
> Thanks in advance

Depending on circumstances I would

1. Separate the input form into two text boxes, one for street number,
the other for street name and modify the query to !!!!something!!!! like
WHERE IItemAddress LIKE '*" & Me!StreetNumber &
AND IItemAddress LIKE '*" & Me!StreetName;
or
2. Write a Public Function which parsed txtSearchCriteria into Tokens and
searched for these in IItemAddress;
or
3. Write a Public Function which used a Regular Expression to Search a
Pattern created from txtSearchCriteria;
or
4. Train the users to use the built-in Find (any part of field) Access
form utility;
or
5. Develop a pull down combo-box selector to replace the text box find
procedure; the list for the selector would probably use some kind of
function to show 100 Smith Street as ID (hidden column zero) Smith
(column one) 100 Column two).



--
lyle fairfield

Re: Problem with Searching Criteria on a search form

redpears007@hotmail.com wrote:
> On 17 Oct, 14:14, OldPro wrote:
>
>>On Oct 17, 7:16 am, redpears...@hotmail.com wrote:
>>
>>
>>
>>
>>
>>
>>>Here's a question regarding my search criteria below. Apologies if
>>>this is a studpidly simple question, but my brain has been frazzled by
>>>this one! :)
>>
>>>The code below currently searches for text string as entered into the
>>>keyword (txtSearchCriteria)
>>
>>>The trouble is the user can input an address to search on i.e. '100
>>>anystreet'. The code will search just for that but if the underying
>>>table field contains for example '100 (the castle) Anystreet' the
>>>search will not find it'
>>
>>>Its no good telling the user to only put straight addresses in, as
>>>they just do whatever they want anyway. :)
>>
>>>So the question is, what is the code below missing to find the
>>>criteria thats entered. I suppose i am looking to find whatever
>>>keywords the user enters wherever they occur in the field and they
>>>don't have to be in any order!
>>
>>> 'If the search box is blank, show all records
>>> If IsNull(Me![txtMonName]) Then
>>> sSQL = "SELECT ItemID, ItemAddress " & _
>>> "FROM tItems " & _
>>> "WHERE ItemAddress Is Not Null " & _
>>> "ORDER BY ItemID"
>>> Else 'otherwise, return the unique (DISTINCTROW) records with a
>>>record that matches
>>> sSQL = "SELECT DISTINCTROW ItemID, ItemAddress " & _
>>> "FROM tItems " & _
>>> "WHERE IItemAddress LIKE '*" & Me!txtSearchCriteria &
>>>"*'" & _
>>> "ORDER BY ItemID"
>>> End If
>>
>>>Any help would be most appreciated.
>>
>>>Thanks in advance
>>
>>Hard to tell... is there a space between "*'" and "ORDER BY ItemID"?
>>There should be- Hide quoted text -
>>
>>- Show quoted text -
>
>
> OldPro, there is no problem with the code above, it works fine, when
> finding a single text string that is!.
>
> Put simply, we have entered search criteria of '100 Anystreet' into
> the search box,
>
> The field 'ItemAddress', in the table stores the data to be searched
> on.
>
> At data entry the address may have been entered in that table as '100
> (formerly 99a) Anystreet'. Therefore our search, as it is, will not
> find that record.
>
> Therefore we need to tweak the code to be able to pick out '100' and
> 'anystreet' in that field. It currently will only find both if they
> exactly the same as in the underlying table field.
>
> The question is how does the search part of the code need to be
> changed to do that. I have tried everything i can think of.
>

I created a search routine a couple of weeks ago. We are provided a
list of buildings that have asbestos, lead, and other toxix substances
by the owners of the buildings. When we enter those addresses from the
owner, in the AfterUpdate event of the form, I parse out each word in
the address and remove directionals and things like Ave and St and etc.
Each word not excluded is then created into a Soundex value and stored
into a table to hold the Soundex values.

When someone creates a new record for a bid I then parse out each word
in that address, excluding directionals/etc, and look for that word in
the soundex file. There are some limitations to Soundex but it is more
accurate when looking for words that can be misspelled. For example,
how may ways are there to misspell "Ghirardelli".

Now you may not need something like Soundex. Perhaps do what I did and
parse out each word, remove directionals and concatenate your Where
string to look at multiple words.
Where word like "*Test*" Or Word Like "*Sample*"...

I've created many search forms where I use FindFirst in continuous forms
to find the first record in the list. I can then use FindNext by
pressing the F3 key to find the next matching record.