INSTR Statement Access 2000 - PLease help..Under time pressure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

SCOTSM12RP

Good afternoon,

Having used this forum a few days a go, I did think my problem was solved
but, I forgot about a "but.........".

I was asking how to build up a search string and then use a simple INSTR
statement.

If I have a search string of "SCOTS,NSM,N4NOS" and an item number of
SCOTSM12RP. Is there a way that I could search for SCOTS or NSM or N4NOS
within the item number. Ps my code is rubbish !!
 
If I understand you correctly, yes. You will need one more function though
than InStr().

Example:
Public Sub TestSplit()
Dim strSplit() As String, i As Integer
strSplit = Split("SCOTS,NSM,N4NOS", ",")
For i = LBound(strSplit()) To UBound(strSplit())
Debug.Print strSplit(i)
Debug.Print InStr("SCOTSM12RP", strSplit(i))
Next i
End Sub
 
SEAN DI''''ANNO said:
SCOTSM12RP

Good afternoon,

Having used this forum a few days a go, I did think my problem was solved
but, I forgot about a "but.........".

I was asking how to build up a search string and then use a simple INSTR
statement.

If I have a search string of "SCOTS,NSM,N4NOS" and an item number of
SCOTSM12RP. Is there a way that I could search for SCOTS or NSM or N4NOS
within the item number. Ps my code is rubbish !!
Click to expand...


** If MyField is required and cannot contain null values **
Just add up the result of each instr function, that way if any of them are
greater than zero, then the sum is greater than zero.

WHERE
(InStr([MyField],"SCOTS")+InStr([MyField],"NSM")")+InStr([MyField],"N4NOS"))>0

If there might be nulls you need to replace [MyField] with Nz([MyField])
 
Hi Wayne,

Thank you for your quick reponse and I think your solution will solve my
dilemma but I need to add on more detai, if you don't mind.

The purpose of what Im doing is a datasbase which will monitor selling
campaigns. SO far I (using this forum) have a screen which collects all of
the details for a new campaign e.g. No Smoking, Start Dat, Expiry Date and
the Item numbers which make up the campaign. These items are held in a
string called "CampaignSearchString". What I want to do if possible is.

In a query, call up your function to tell me if an item has been sold within
the searchstring which is specific to the campaign. So...the query will link
to the orders file listing all of the items sold and the campaign file,
listing the searchstring. Therefore I would to call up the function sending
it the value of Item Number and SearchString. If this is possible cwould you
mind telling me what I need to add to my query to call up the function and
return a value which, when greater than 0 means...item was sold.

Thank you Wayne and thank you Brian. I did use that technique but I want
this to be dynamic. I.e. that would only work for one fixed campaign and not
several.


I.e. is it ItemSold: TestSplit
 
ItemSold: SplitString([CampaignSearchString],[CatalogCode],[ItemNo])

Function SplitString(ByVal CampaignSearchString As String, CatalogCode As
String, itemNo As String) As Integer
Dim strSplit() As String, i As Integer, EachItem As Integer
strSplit = Split(CampaignSearchString, ",")
For i = LBound(strSplit()) To UBound(strSplit())
EachItem = InStr(CatalogCode, strSplit(i)) + InStr(itemNo, strSplit(i))
If EachItem > 0 Then
SplitString = 1
Exit Function
End If
Next i
End Function

Crickey...Am I starting to get a brain. Using the help from both of you..Is
this (as above) the most effective way) ??



Brian Wilson said:
SEAN DI''''ANNO said:
SCOTSM12RP

Good afternoon,

Having used this forum a few days a go, I did think my problem was solved
but, I forgot about a "but.........".

I was asking how to build up a search string and then use a simple INSTR
statement.

If I have a search string of "SCOTS,NSM,N4NOS" and an item number of
SCOTSM12RP. Is there a way that I could search for SCOTS or NSM or N4NOS
within the item number. Ps my code is rubbish !!
Click to expand...


** If MyField is required and cannot contain null values **
Just add up the result of each instr function, that way if any of them are
greater than zero, then the sum is greater than zero.

WHERE
(InStr([MyField],"SCOTS")+InStr([MyField],"NSM")")+InStr([MyField],"N4NOS"))>0

If there might be nulls you need to replace [MyField] with Nz([MyField])
Click to expand...
 
SEAN DI''''ANNO said:
ItemSold: SplitString([CampaignSearchString],[CatalogCode],[ItemNo])

Function SplitString(ByVal CampaignSearchString As String, CatalogCode As
String, itemNo As String) As Integer
Dim strSplit() As String, i As Integer, EachItem As Integer
strSplit = Split(CampaignSearchString, ",")
For i = LBound(strSplit()) To UBound(strSplit())
EachItem = InStr(CatalogCode, strSplit(i)) + InStr(itemNo, strSplit(i))
If EachItem > 0 Then
SplitString = 1
Exit Function
End If
Next i
End Function

Crickey...Am I starting to get a brain. Using the help from both of
you..Is
this (as above) the most effective way) ??
Click to expand...



I would not want to split the campaign search string up each and every time.
I would split once and re-write the query so that it read literally:

WHERE
(InStr([MyField],"SCOTS")+InStr([MyField],"NSM")")+InStr([MyField],"N4NOS"))>0


If you have never used this technique before, you get the querydef and alter
its SQL property. So your code can be of the form:

If RedefineTheQuery(strSearchString As String) = True
' Go ahead and run the report...
Else
MsgBox "Houston, we have a problem"
End If

PS: I hope you took my point about nulls - it is important.



Brian Wilson said:
SEAN DI''''ANNO said:
SCOTSM12RP

Good afternoon,

Having used this forum a few days a go, I did think my problem was
solved
but, I forgot about a "but.........".

I was asking how to build up a search string and then use a simple
INSTR
statement.

If I have a search string of "SCOTS,NSM,N4NOS" and an item number of
SCOTSM12RP. Is there a way that I could search for SCOTS or NSM or
N4NOS
within the item number. Ps my code is rubbish !!
Click to expand...


** If MyField is required and cannot contain null values **
Just add up the result of each instr function, that way if any of them
are
greater than zero, then the sum is greater than zero.

WHERE
(InStr([MyField],"SCOTS")+InStr([MyField],"NSM")")+InStr([MyField],"N4NOS"))>0

If there might be nulls you need to replace [MyField] with Nz([MyField])
Click to expand...
Click to expand...
 
To use it in a query, it would be
ItemsSold:TestSplit([FieldNameWithStringToSearch], "String,Of,Search,Terms")

You will need to modify the Sub to a Function and the function to accept the
argument. You may also want to add a second argument for the string with
search values.

Example:
Public Function TestSplit(strSearch As String, strSplitInput As String) As
Boolean
Dim strSplit() As String, i As Integer
strSplit = Split(strSplitInput, ",")
For i = LBound(strSplit()) To UBound(strSplit())
If InStr(strSearch, strSplit(i)) > 0 Then
TestSplit = True
Exit Function
Else
TestSplit = False
Next i
End Function


However, in a query, you may also want to use Like as the criteria.
Like "*scots*"
will find records with the string "scots" anywhere in the field. If you use
Like "scots*"
it would have to start with "scots". You could then do a Count of the
records returned.
 
Also, just to note that Instr was broken in the A2K first
release. (You should have at least SR1 when using A2K.)

(david)
 
Back
Top