Solved

instr problem in vba

Posted on 2014-09-16
8
339 Views
Last Modified: 2014-09-16
I am using an if statement to determine another process but I have an instr problem:

If (InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("LookFor").Value) <> 0) And (RstWordGroups.Fields("MarqueAlias").Value = RstAutoRange.Fields("Marque").Value) Then

Open in new window



Fields("ConcatenatedUnwantedRemoval").Value = "C 320 AVANTGARDE"
RstAutoRange.Fields("LookFor").Value = "C 32"

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
0
Comment
Question by:PeterBaileyUk
8 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40324850
You could look for a "word" with a trailing Space, "C 32 ":

InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("LookFor").Value & " ")

/gustav
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40324852
Can I cheat? Can I say that you are looking for C32 either at the end of the string, or C32 followed by a space?

If so, then the INSTR then becomes:

InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value & " ", RstAutoRange.Fields("LookFor").Value & " ") <> 0)
0
 

Author Comment

by:PeterBaileyUk
ID: 40324861
various clients do this:

"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
0
 

Author Comment

by:PeterBaileyUk
ID: 40324863
i will try the new instr see if it cures
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:PeterBaileyUk
ID: 40324976
it worked
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40324977
ok i shared the points hope thats ok
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324978
That's fine.
0
 
LVL 57
ID: 40324992
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now