Create a Search button in a form.

[ruthcali]

I am using Access97.

I have a table with a field called Address. Sample data for that field is: 123 Main Street, 852 L Street, 456 Green St., 55 Maine St., etc.

I have a continuous form based on the table and I want to make a button that lets the user search for similar addresses. So if he clicks on the 123 Main Street record, then clicks my Search command button, I want the form to filter for all similar addresses and display, for example, 55 Maine St, 98 Main Street, 32 Mane St., etc.

I have it working to a degree. This is what I have done. I was just wondering if there is a better way to do it that is more accurate since my results are not exactly what I want:

Form.FilterOn = True

'txtAddress looks for the first blank space in the Address string and takes everything
'to the right of it. So 1234 Main Street would be Main Street.
txtAddress = Mid([Address], (InStr(1, [Address], " &quot + 1))

'txtLength calculates the length of txtAddress, divides it in half, adds 2 and
'rounds the value so it's a whole number. In the Main Street example, txtLength would
'be the value 7. (Main Street has 11 characters, divided in half would be 5.5, add
'2 would be 7.5, and round to 7).
txtLength = Int(Len(txtAddress) / 2) + 2

'txtAddressPortion starts from txtAddress and takes txtLength number of charaters.
'in the Main Street example, the txtAddressPortion would be Main St.
txtAddressPortion = Left([txtAddress], [txtLength])

strFilter = “Address like " & "'" & "*" & txtAddressPortion & "*" & "'"
form.filter=strFilter
End Sub

txtLength is just an arbitrary calculation I decided on. (Cut the length in half and add 2).
My method works to a degree, but, in the 123 Main Street example above, my method unfortunately would NOT include 55 Maine St and 32 Mane St., etc.

Also, if the address was 852 L Street, my formula is searching for the string ‘L Stre’. Therefore, it would return a value of 355 Capitol Street which would be incorrect.

Is there a more accurate way to do what I am trying to do? I see lots of web pages with Search buttons and they work well.

Thanks,
Ruth

 

[BlackKnight]

Hi,

I think you have to use SoundEx() which groups certain letters together and assigns them a numerical code that can be used to find the similiar spellings.

There may be a FAQ here regarding SoundEx.

Have a good one!
BK

 

[ruthcali]

BK,
thanks for writing. i have never heard of soundex before!

i looked at the Help for Access97, but there was nothing about soundEx. i'll search around tek tips faqs and look around the internet.

thanks again.

 

[WildHare]

Soundex is not a built in function in Access. (MS has to go a ways to catch up to Ansa's Paradox ca. 1987.

However I just saw a post with a soundex function on TekTips here yesterday (don't remember the exact forum or poster). Do a search and you'll find it, and try it out.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm