InStr Function Help

graviz

graviz

Registered User.
Local time
Today, 11:58
Joined
Aug 4, 2009
Messages
167
I have a field that provides a 1 or 0 based upon if a field contains "-k"

K_Code: IIf(InStr([service_code_string],"-k"),1,0)

Is there a way to search for both "-k" and "QP"

I tried with no luck.
K_Code: IIf(InStr([service_code_string],"-k", "QP"),1,0)

Any idea?
 
More like:

IIf(InStr([service_code_string],"-k") OR InStr([service_code_string], "QP"),1,0)
 
pbaldy said:
More like:

IIf(InStr([service_code_string],"-k") OR InStr([service_code_string], "QP"),1,0)
Click to expand...

So I'm going to have 28 codes to search for in one field. Is that the best way to handle it without using SQL?
 
graviz said:
So I'm going to have 28 codes to search for in one field. Is that the best way to handle it without using SQL?
Click to expand...

pbaldy's reply is appropriate when there are two or three (maybe even four) tests to perform. It is easy to see how it could get trickier as the number of tests grows. Since you seem to have (up to 28?) tests, you would be much better suited using a VBA Function that returns an Integer reply of 1 or 0 depending on the results of the tests.

-- Rookie
 
I have trouble hitting moving targets. ;)

Another option is a table with the items, with a LEFT JOIN in the query. A formula like:

IIf(IsNull(FieldName), 0, 1)

Should return the desired result.
 
MSAccessRookie said:
pbaldy's reply is appropriate when there are two or three (maybe even four) tests to perform. It is easy to see how it could get trickier as the number of tests grows. Since you seem to have (up to 28?) tests, you would be much better suited using a VBA Function that returns an Integer reply of 1 or 0 depending on the results of the tests.

-- Rookie
Click to expand...

Is it possible to write a function in vba and use it in QBE mode in access (using the expression builder)?
 
That happens to be a very common way to use a Function. in the QBE, you could place something like:

YourValue:YourFunction(YourStringtoTest)
 
MSAccessRookie said:
That happens to be a very common way to use a Function. in the QBE, you could place something like:

YourValue:YourFunction(YourStringtoTest)
Click to expand...

Based on what I'm trying to do would you be able to help me with the code? I've never created a function to use before.
 
Based on your description, an If/ElseIf/EndIf Statement should suit your needs, making a Function similar to the following:
Code: 
[FONT=Arial][SIZE=2] 
Function YourFunction(ServiceCodeStr[SIZE=2]ing [/SIZE]AS String) AS [SIZE=2]Integer[/SIZE]

[/SIZE][/FONT][FONT=Arial][SIZE=2][FONT=Arial][SIZE=2][SIZE=2]    If [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"-k") [SIZE=2]<> [/SIZE]0
        [/FONT]YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"QP") [SIZE=2]<> [/SIZE]0
        [/FONT]YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"<NextString>") [SIZE=2]<> [/SIZE]0
        [/FONT]YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"<NextString>") [SIZE=2]<> [/SIZE]0
        [/FONT]YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]Else[/SIZE][/SIZE][/FONT][FONT=Arial]
        [/FONT]YourFunction = 0
     EndIf

[/SIZE][/FONT]


Note that you will need to fill in all of the remaining ElseIf statements, and make any other changes that are appropriate to your environment
 
MSAccessRookie said:
Based on your description, an If/ElseIf/EndIf Statement should suit your needs, making a Function similar to the following:
Code: 
[SIZE=2][FONT=Arial]Function YourFunction(ServiceCodeStr[SIZE=2]ing [/SIZE]AS String) AS [SIZE=2]Integer[/SIZE][/FONT]
 
[/SIZE][FONT=Arial][SIZE=2][FONT=Arial][SIZE=2][SIZE=2]   If [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"-k") [SIZE=2]<> [/SIZE]0[/FONT]
YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"QP") [SIZE=2]<> [/SIZE]0[/FONT]
YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"<NextString>") [SIZE=2]<> [/SIZE]0[/FONT]
YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]ElseIf [/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2](InStr([[/SIZE][/SIZE][/FONT][FONT=Arial][SIZE=2][SIZE=2]ServiceCodeStr[SIZE=2]ing[/SIZE][/SIZE][/SIZE][/FONT][FONT=Arial]],"<NextString>") [SIZE=2]<> [/SIZE]0[/FONT]
YourFunction = 1
     [FONT=Arial][SIZE=2][SIZE=2]Else[/SIZE][/SIZE][/FONT]
YourFunction = 0
     EndIf
 
[/SIZE][/FONT]


Note that you will need to fill in all of the remaining ElseIf statements, and make any other changes that are appropriate to your environment
Click to expand...

Thanks for the help! What is the <> 0 do for the statement? Could you just leave it out?
 
Actually looks like I got it to work. Here is my code:

Function DishNetProg(service_code_string As String) As Integer

If InStr([service_code_string], "KV") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KY") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KT") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "NU") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KS") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "LO") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "LR") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KL") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "L~") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "L#") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "NH") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KZ") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KR") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "N#") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "F*") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "F/") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "B$") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "D?") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "G*") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "G.") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "B(") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "B;") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "B*") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "HT") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "JF") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "KW") Then
DishNetProg = 1
ElseIf InStr([service_code_string], "TM") Then
DishNetProg = 1
Else
DishNetProg = 0
End If
End Function

A couple questions:

If there a way to simplify this code (i.e. loop through all the codes)?
 
Here is a simple method:

Code: 
Function DishNetProg(service_code_string As String) As Integer
Dim Choices() As String, X As Integer
ReDim Choices(28)
Choices() = Split("-k,KY,KT,NU,KS,LO,LR,KL,L~,L#,NH,KZ,KR,N#,F*,F/,B$,D?,G*,B(,B*,HT,JF,KW,TM", ",")

For X = 0 To UBound(Choices)
    If InStr([service_code_string], Choices(X)) Then
        DishNetProg = 1
        Exit Function
    End If
Next X
DishNetProg = 0
End Function
 
Instead of writing all those If, could this be solved by means of a small For loop? Looping through the values in the Array that has all the specific patterns?
Code: 
Function DishNetProg(service_code_string As String) As Integer
    Dim pattStr, fCtr As Integer
    pattStr = Array("KV", "KY", "KT", "NU", "KS", "LO", "LR", "KL", "L~", "L#", "NH", "KZ", "KR", "N#", "F*", "F/", "B$", "D?", "G*", "G.", "B(", "B;", "B*", "HT", "JF", "KW", "TM")
    
    For fCtr = 0 To UBound(pattStr)
        If InStr(service_code_string, pattStr(fCtr)) Then
            DishNetProg = 1
            Exit Function
        End If
    Next
    DishNetProg = 0
End Function
EDIT: Was verrrrrrry SLOW me thinks.. :D
 
Last edited:
billmeye said:
Here is a simple method:

Code: 
Function DishNetProg(service_code_string As String) As Integer
Dim Choices() As String, X As Integer
ReDim Choices(28)
Choices() = Split("-k,KY,KT,NU,KS,LO,LR,KL,L~,L#,NH,KZ,KR,N#,F*,F/,B$,D?,G*,B(,B*,HT,JF,KW,TM", ",")
 
For X = 0 To UBound(Choices)
    If InStr([service_code_string], Choices(X)) Then
        DishNetProg = 1
        Exit Function
    End If
Next X
DishNetProg = 0
End Function
Click to expand...

I'm assuming if one of the service codes contains a "," then I would just use a different notation to seperate the codes by? First time I've seen that function.

Here's a curve ball. I was gioing to create a second function to exclude 3 codes: 6; ;$ and 82

Could I integrate it into this one to include all those but exclude those 3?

Thanks everyone for all your help on this! I'm learning a lot.
 
If you want to use "," as another code to be checked against, you can utilize the code I have given in Post#13.. Or you can change the separator to ;.. However if you change the separator to semi colon and want to use bill's code then be careful as it will split B; into B and ;..

What do you exactly mean by exclude 3 codes? They do not even come in the list above..
 
pr2-eugin said:
If you want to use "," as another code to be checked against, you can utilize the code I have given in Post#13.. Or you can change the separator to ;.. However if you change the separator to semi colon and want to use bill's code then be careful as it will split B; into B and ;..

What do you exactly mean by exclude 3 codes? They do not even come in the list above..
Click to expand...

That's what i thought. I have a service code string where I need to make sure I'm including work orders that have those codes (the 28) but don't have others (i.e. those 3) Does that make sense?
 
pr2-eugin said:
Nope still coming dry.. Could you give an example?
Click to expand...

Work Order #1 Service Code AA BB CC DD EE
Work Order #2 Service Code AA CC DD

I want all work orders that have AA & CC but not BB.

So I wouldn't want work order #1 eventhough it have AA & CC because it also has BB.

How bout now?
 
Is it only going to be 3 codes? If so just integrate to the function..
Code: 
Function DishNetProg(service_code_string As String) As Integer
    Dim pattStr, fCtr As Integer
    pattStr = Array("KV", "KY", "KT", "NU", "KS", "LO", "LR", "KL", "L~", "L#", "NH", "KZ", "KR", "N#", "F*", "F/", "B$", "D?", "G*", "G.", "B(", "B;", "B*", "HT", "JF", "KW", "TM")
    
    For fCtr = 0 To UBound(pattStr)
        If InStr(service_code_string, pattStr(fCtr)) Then
            [COLOR=Red][B]If InStr(service_code_string, "6;") = 0 And InStr(service_code_string, ";$") = 0 And InStr(service_code_string, "82") = 0 Then[/B][/COLOR]
                DishNetProg = 1
                Exit Function
            [COLOR=Red][B]End If[/B][/COLOR]
        End If
    Next
    DishNetProg = 0
End Function
 
pr2-eugin said:
Is it only going to be 3 codes? If so just integrate to the function..
Code: 
Function DishNetProg(service_code_string As String) As Integer
    Dim pattStr, fCtr As Integer
    pattStr = Array("KV", "KY", "KT", "NU", "KS", "LO", "LR", "KL", "L~", "L#", "NH", "KZ", "KR", "N#", "F*", "F/", "B$", "D?", "G*", "G.", "B(", "B;", "B*", "HT", "JF", "KW", "TM")
 
    For fCtr = 0 To UBound(pattStr)
        If InStr(service_code_string, pattStr(fCtr)) Then
            [COLOR=red][B]If InStr(service_code_string, "6;") = 0 And InStr(service_code_string, ";$") = 0 And InStr(service_code_string, "82") = 0 Then[/B][/COLOR]
                DishNetProg = 1
                Exit Function
            [COLOR=red][B]End If[/B][/COLOR]
        End If
    Next
    DishNetProg = 0
End Function
Click to expand...

For now it is but in the future it may increase. Could I use the same array logic if it does?

If InStr(service_code_string, pattStr(fCtr)) = 0
 
You must log in or register to reply here.

Similar threads

J
instr in a filter
Replies
9
Views
439
John Sh
J
F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
901
June7
June7
H
Solved Sum of one filed based on Yes/No field value
Replies
10
Views
518
hrdpgajjar
H
D
Solved Combining IIf Statements
3 4 5
Replies
91
Views
2,657
DHookom
DHookom
D
Solved Nesting IIF Statement with functions that end in (field)
Replies
13
Views
517
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom