Comparing two fields

  • Thread starter Thread starter printman2000
  • Start date Start date
P

printman2000

I am doing a simple IIF statement that comapres two fields(both
lastnames). However, the first field(LastName) has only the first
letter capitolized and the rest lower case. The second
field(FamilySortField) is in all caps. I need to compare the letters
and not be case sensitive. Is that possible?

Here is the expression...

=IIf([LastName] = [FamilySortField],"",[LastName])
 
Like Duane said - it is NOT case sensitive. But if you are still not finding
a match when you think you should, try using the TRIM() function to remove
any extra spaces first. And if you want to make doubly sure, you can use the
StrConv() function to change both to the same case...I would use upper case.

See Access Help for explanations on how to use these functions.
And yes you can nest them:

=IIf(strConv(Trim([LastName]), vbUpperCase) = _
strConv(Trim([FamilySortField]), vbUpperCase), _
"",[LastName])

By the way, YOU DO KNOW that your IIF statement will use "" if a match is
found? Is that what you want? I mean your logic looks backwards to me, but
maybe that is what you intended. If not, then try the opposite:

=IIf(strConv(Trim([LastName]), vbUpperCase) = _
strConv(Trim([FamilySortField]), vbUpperCase), _
[LastName],"")
'This will use the [LastName] if a match is found.

jmonty
 
By the way, YOU DO KNOW that your IIF statement will use "" if a match is
found? Is that what you want? I mean your logic looks backwards to me, but
maybe that is what you intended. If not, then try the opposite:

=IIf(strConv(Trim([LastName]), vbUpperCase) = _
strConv(Trim([FamilySortField]), vbUpperCase), _
[LastName],"")
'This will use the [LastName] if a match is found.

jmonty
Click to expand...

Yeah, it looks weird. But I am only wanting to print a last name if it
is not the same.

Thanks
 
Back
Top