If statement..... Possible Match


L

learning_codes

Hi,

I'm trying to make two fields that are similar.

I create but it won't give me the result.


FieldA : Baseball
FieldB: Baseb
Possible Match: Yes


FieldA: Football
FieldB: Football
Possible Match: Yes

FieldA: Baseball
FieldB: Football
Possible Match: NO


Please help me if I did the right or not. Your help would be much
appreciated.

Possible Match: IIF([FieldA] like [FieldB],"Yes","NO")

Thanks
 
Ad

Advertisements

M

Marshall Barton

I'm trying to make two fields that are similar.

I create but it won't give me the result.


FieldA : Baseball
FieldB: Baseb
Possible Match: Yes


FieldA: Football
FieldB: Football
Possible Match: Yes

FieldA: Baseball
FieldB: Football
Possible Match: NO []
Possible Match: IIF([FieldA] like [FieldB],"Yes","NO")


The Like operator is only useful when the right side operand
includes wildcard characters, In this case you want to use

Possible Match: IIF(FieldA Like FieldB & "*", "Yes", "NO")
 
J

John W. Vinson

Hi,

I'm trying to make two fields that are similar.

I create but it won't give me the result.


FieldA : Baseball
FieldB: Baseb
Possible Match: Yes


FieldA: Football
FieldB: Football
Possible Match: Yes

FieldA: Baseball
FieldB: Football
Possible Match: NO

Sure it's a possible match. Both values contain the string Ball.

Please help me if I did the right or not. Your help would be much
appreciated.

Possible Match: IIF([FieldA] like [FieldB],"Yes","NO")

If you define "similar" as "The first characters of FieldA are the same as the
entirity of FieldB" you can use

IIF([FieldA] LIKE [FieldB] & "*", "Yes", "No")

or

IIF(InStr([FieldA], [FieldB]) = 1, "Yes", "No")

If you define "similar" as "fieldB in its entirity is contained within FieldA"
then

IIF([FieldA] LIKE "*" & [FieldB] & "*", "Yes", "No")

or change the =1 to >0 in the InStr.

John W. Vinson [MVP]
 
T

Tom Lake

Hi,

I'm trying to make two fields that are similar.

There's a function called Soundex (not included in Access)
that might do what you want. There should be a free 3rd
party version for Access, though. If you can't find one,
let me know and I'll write one in VBA. It's fairly simple.

Tom Lake
 
T

Tom Lake

Hi,

I'm trying to make two fields that are similar.

I create but it won't give me the result.

I found a SOUNDEX function in VBA. It's at

http://www.j-walk.com/ss/excel/tips/tip77.htm

It's written for Excel but will also work in Access.

example:

print SOUNDEX("baseball")
B214
print SOUNDEX("football")
F314
print SOUNDEX("basebll")
B214
print soundex("bayceball")
B214

See how bayceball and baseball sound alike and soundex shows that?

Tom Lake

Tom Lake
 
Ad

Advertisements

M

Michael Gramelspacher

@y80g2000hsf.googlegroups.com>, (e-mail address removed)
says...
Hi,

I'm trying to make two fields that are similar.

I create but it won't give me the result.


FieldA : Baseball
FieldB: Baseb
Possible Match: Yes


FieldA: Football
FieldB: Football
Possible Match: Yes

FieldA: Baseball
FieldB: Football
Possible Match: NO


Please help me if I did the right or not. Your help would be much
appreciated.

Possible Match: IIF([FieldA] like [FieldB],"Yes","NO")

Thanks
Two strings are similar in this example, if either is contained
within the other, and if the smaller string is at least half as
long as the longer. If the initial character of both strings
must merely be the same, then good solutions have already been
furnished.

In this example Baseball could be FieldA and seball could be
FieldB. For me, using literal strings makes it easier to see
what is happening.

?iif((instr("seball","Baseball")>0) or (instr
("baseball","seball")>0),iif((((len("seball")/len("baseball") >
=.5)and (len("seball")/len("baseball")<=2)) and ((len
("baseball")/len("seball") >=.5)and (len("baseball")/len
("seball")<=2.0))),"yes","no"),"no")

The above seems to work, but I have a sneaking suspician than
it could be much simiplied.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top