Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Compare strings in Access

Posted on 2007-04-05
15
617 Views
Last Modified: 2008-01-09
I would like to create a routine that will parse out spaces and perform a comparison on a varchar 255 field

desc
SO GLASS BEER 8.25 OZ PILSNER
GLASS BEER 8Z PILSNER HVY 12

In this example there are 3 strings that would validate - yes?
Any thoughts on where and how to start?  As a note, I should be considered a beginner at Access.

0
Comment
Question by:franco2
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 75
ID: 18861000
Well, you can use the Replace() function to parse out spaces.
What is the comparison you want to make?

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18861571
Welcome Beginner...
Parsing follows rules. So....in order to know where you want your data split, we need to know the rules. You say out of the data you pasted in, there are 3 valid strings...how are those identified? And...what determines a good string?

That being said....how are you wanting to perform this comparison and what do you plan on doing with the results?
J
0
 
LVL 75
ID: 18861609
<jw ... the LTM thing ... man, I'm glad I've had that worked out for years>


ok ... to address just the removing spaces part, for example:

Replace("SO GLASS BEER 8.25 OZ PILSNER"," ","")  returns  SOGLASSBEER8.25OZPILSNER,

Replace can be used in code, a query or an expression in the control source in a Form/Report

The basic syntax is:

Replace(expression, find, replace)
Where, in the example above ...
expression = "SO GLASS BEER 8.25 OZ PILSNER"  <ie, the field/string you are working on to parse out>
find = " "   <a space>
replace= ""   <an empty string>

mx

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

 
LVL 26

Expert Comment

by:jerryb30
ID: 18861770
I think we are looking for a split to determine separate words.
0
 
LVL 75
ID: 18861814
No clue so far :-(

An *interactive* session would be ... nice :-)

Until then ...

mx
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18861862
maybe, air code
function compareit(string1 as string, string2 as string) as integer
dim var1 as variant
dim var2 as variant
dim i as integer
dim j as integer
var1 = split(string1, " ")
var2 = split(string2, " ")
if ubound(var1) > ubound(var2) then
for i = 0 to ubound(var1) -1
 for j = 0 to ubound(var2)-1
    if var1(i) = var2(j) then
        compareit = compareit +1
   next i
     next j
else
 if ubound(var1) > ubound(var2) then
for i = 0 to ubound(var2) -1
 for j = 0 to ubound(var1)-1
    if var2(i) = var2(i) then
        compareit = compareit +1
   next i
     next j
endif
end function

create a new module named module1
Paste in code between function.... end function

Then in a query
select desc1, descr2, compareit(desc1, descr2) as equalwords from yourtable

but it depends on if descr1 and descr2 are in the same table....
need a lot more info on table(s), field(s), etc.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18861950
<and very clever using beer in your example. sure to get some response.>
0
 

Author Comment

by:franco2
ID: 18863298
Back on line - and yes beer was a bit of a bait for help...sorry, but who doesn't like an ice cold beer.

Great feedback so far...haven't tried anything out.  The purpose is to be able to look at string a and sequencially look at string b to see if there are like characters...thus, both would be loosely identified as being the same product.

ie beer in string a would match to beer in string b  

Once I have this complete, then I compare on additional attributes(not part of this question)to make sure they are the same size shape and beerie''ness....

Creation of the module - wow.  I can try, but unsure.

I am looking at about 60,000 records, this would at least help 'link' some items together....

Thanks in advacnce.


0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18863456
Same table. Two different tables?
Any other linking factors between two tables (if two tables)?
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 50 total points
ID: 18863526
How do you introduce the string into the "search"? Will this be for Same-Field-Comparison only?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18863583
The module was real bad.
This works.
Function compareit(string1 As String, string2 As String) As Integer
Dim var1 As Variant
Dim var2 As Variant
Dim i As Integer
Dim j As Integer
var1 = Split(string1, " ")
var2 = Split(string2, " ")
If UBound(var1) > UBound(var2) Then
For i = 0 To UBound(var1)
 For j = 0 To UBound(var2)
    If var1(i) = var2(j) Then
        compareit = compareit + 1
        End If
   Next j
     Next i
Else
 
For i = 0 To UBound(var2)
 For j = 0 To UBound(var1)
    If var2(i) = var1(j) Then
        compareit = compareit + 1
        End If
   Next j
     Next i
End If
End Function
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 200 total points
ID: 18864804
If you have a single table, and you want to compare one field with all other fields in the table,
SELECT a.descr, b.descr, compareit([a].[descr],[b].[descr]) AS WordMatches
FROM yourTable AS a,yourTable AS b
WHERE (((a.descr)<[b].[descr]) AND ((b.descr)<>[a].[descr]))
ORDER BY a.descr;

Still awaiting more info.......
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18864825
I think you scared him off Jerry!
0
 

Author Comment

by:franco2
ID: 18867290
Not scard off but having troubles with my internet connection.  Same table but not sure on wordmatches....as in the example, beer may be in different parts of the desciption....I will try out the suggestions later as I am afraid to drop you....I will test this eve and post fidings tomorrow.
Cheers.
0
 

Author Comment

by:franco2
ID: 18877528
Split the point slighty - all have a hand in the solution.
compareit was the key - I can now 'assume' that if the description compared on 3>, it is the same product.

Much apprciated.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
update query 4 25
Access 2010 change CurrentUser 5 33
sql server query 12 24
Populating datasheet subform from VBA 6 14
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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