InStr Function

[aco636]

Hi there

As always, any help gratefully received.

I am working on a project to check data in two databases in an attempt to match and update common ref to both systems. The two systems had been run as stand alone for some time and suffered a variety of data entry "standards".

I have written a script that does what I want but part of it, the address check, works but I dont know why.

Let me explain, I understood the InStr() function to return an integer for the place where the item required was found. So the statement

If InStr("AAAA", "A") > 0 THen
WScript.Echo "Yippee"
Else
WScript.Echo "Not found"
End If

An in fact the above works fine

So I dont understand whythe reverse is true with my scrpt below. To make it return the right result I am having to alter the statement to:
If Not InStr() > 0
To get the desired result. I have altered the script to provide a simple example

Any help appreciated
regards ACO

Dim arrPlusAdd, iPlusAdd, strToSearch, strCompareAddress, iAddMatch

strToSearch = "7 THE DRIVE HIGH HOPE HERTS"
strPlusAddress = "7 THE DRIVE HIGH HOPE"

arrCashAdd = Split(strToSearch, " ")
arrPlusAdd = Split(strPlusAddress, " ")

iAddMatch = 0

For iPlusAdd = 0 To UBound(arrPlusAdd)

	If Not InStr(arrPlusAdd(iPlusAdd), strToSearch) > 0 Then
	
			strCompareAddress = strCompareAddress & " " & arrPlusAdd(iPlusAdd)
			iAddMatch = iAddMatch + 1
			
	End If
	
Next
WScript.Echo strCompareAddress & " " & iAddMatch & " matched of " & UBound(arrCashAdd) + 1

 

[EBGreen]

If you put If Not InStr(arrPlusAdd(iPlusAdd), strToSearch) > 0 Then, then you are testing for strings that do not match. To demonstrate this, run this:

Dim arrPlusAdd, iPlusAdd, strToSearch, strCompareAddress, iAddMatch

strToSearch = "7 THE DRIVE HIGH HOPE HERTS"
strPlusAddress = "7 THE DRIVE HIGH HOPE"

arrCashAdd = Split(strToSearch, " ")
arrPlusAdd = Split(strPlusAddress, " ")

iAddMatch = 0

For iPlusAdd = 0 To UBound(arrPlusAdd)
	WScript.Echo "Testing " & arrPlusAdd(iPlusAdd) & " FOR " & strToSearch
    If Not InStr(arrPlusAdd(iPlusAdd), strToSearch) > 0 Then
    		
            strCompareAddress = strCompareAddress & " " & arrPlusAdd(iPlusAdd)
            iAddMatch = iAddMatch + 1
            
    End If
    
Next
WScript.Echo strCompareAddress & " " & iAddMatch & " matched of " & UBound(arrCashAdd) + 1

Notice that you are essentially checking each word to see if an entire sentence lies within it. Obviously none of the tests should match.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[aco636]

Hi there

Do yoou know how long I have been looking atthis.... Wrong way around, Doh!

Thanks for your help, appreciate it.

regards ACO