comparing field with mixed case

[K Essmiller]

We have a db of medical providers. There is a provider ID that would be for
say a hospital and a location code that would be for the individual doctors
within the hospital. Our software program stores the location field in mixed
case, so I can have provider 12345 with locations 'aim', 'aiM', 'aIm', 'aIM',
etc and they all look the same to Access. I need to tie several tables
together using these two fields but I get duplicates due to the location
code. I tried the asc function, but that only looks at 1 position. Is there a
way to do this without having to use 3 asc comparisons?

 

[Phil Smith]

so I can have provider 12345 with locations 'aim', 'aiM', 'aIm', 'aIM',

So those are four distinct values? Either write a function which would
compare all three, or a quick and dirty way:

(Chr$(tbl.location)+(chr$(mid$(tbl.location,2,1))*256)+(chr$(mid$(tbl.location,3,1))*65536))

Will give you a single long number unique to any location string.
Compare that to

(Chr$(tbl2.location)+(chr$(mid$(tbl2.location,2,1))*256)+(chr$(mid$(tbl2.location,3,1))*65536))

and you have your match.

Phil

 

[John Spencer]

Take a look at using the VBA StrComp function or the Instr function with
Binary compare argument.

WHERE StrComp([location],"aim",0) = 0

Or

WHERE Instr(1,[location],"aim",0) > 0



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County