PeterBaileyUk
asked on
instr problem in vba
I am using an if statement to determine another process but I have an instr problem:
Fields("ConcatenatedUnwant
RstAutoRange.Fields("LookF
How do I change this so that the instr doesnt pick up in this event?
RstAutorange has in it:
"C32"
"C 32"
"C 320"
"C320"
So I only want it to be true if the particular string is in the string
in my example
"C 320 AVANTGARDE" c 32 gets by the instr
I am in vba
If (InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("LookFor").Value) <> 0) And (RstWordGroups.Fields("MarqueAlias").Value = RstAutoRange.Fields("Marque").Value) ThenFields("ConcatenatedUnwant
RstAutoRange.Fields("LookF
How do I change this so that the instr doesnt pick up in this event?
RstAutorange has in it:
"C32"
"C 32"
"C 320"
"C320"
So I only want it to be true if the particular string is in the string
in my example
"C 320 AVANTGARDE" c 32 gets by the instr
I am in vba
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i will try the new instr see if it cures
ASKER
it worked
ASKER
ok i shared the points hope thats ok
That's fine.
Is your problem case sensitivity, the spaces, or both?
Couple of comments:
1. On Case sensitivity, you can:
a. use UCase() to force a string to all upper case, then do the compare.
b. place your check procedure in its own module, then use the Option Compare statement. There are three settings, Binary, Text, and Database. Each works differently:
Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:
A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø
Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:
(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)
Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.
2. As for the spaces, when I do certain compares, I use a procedure to remove all white space (spaces, tabs, etc) and "collapse" the string so that it's nothing but characters.
It's easy to write a routine like that using replace(). i.e.
strText = Replace(strText, " ", "")
I find compares on collapsed strings more accurate.
HTH,
Jim.
Couple of comments:
1. On Case sensitivity, you can:
a. use UCase() to force a string to all upper case, then do the compare.
b. place your check procedure in its own module, then use the Option Compare statement. There are three settings, Binary, Text, and Database. Each works differently:
Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:
A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø
Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:
(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)
Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.
2. As for the spaces, when I do certain compares, I use a procedure to remove all white space (spaces, tabs, etc) and "collapse" the string so that it's nothing but characters.
It's easy to write a routine like that using replace(). i.e.
strText = Replace(strText, " ", "")
I find compares on collapsed strings more accurate.
HTH,
Jim.
ASKER
"C32"
"C 32"
"C 320"
"C320"
I dont know which part of the string in will be in it might be first or in middle (surrounded by space)
Here is some actual strings for this example:
concatenated
C 320 AVANTGARDE
C 320 AVANTGARDE AUTO
C 320 AVANTGARDE SE
C 320 AVANTGARDE SE CDI
C-CLASS C 320 CDI AVANTGARDE
C-CLASS C 320 CDI AVANTGARDE AUTO