Comparing Access Fields with Outlook Data

[JonOfAllTrades]

Good morning, all.
I have a procedure that searches for an Outlook contact's company name
in Access with "SELECT ... WHERE Name = " & OLContact.CompanyName. However,
a handful of company names in Outlook have non-breaking spaces. Access does
not consider a NBS to be equal to a normal space when doing a text
comparison, so it does not find a matching record.
Can I use Replace() on OLContact.CompanyName? If so, how do I specify
a special character to replace? If I use the ASCII code, Access searches for
the string "160," not the character #160/0xA0. Is there a looser text
comparison mode available?
Personally, I like the MySQL part of the DB; it's not even
case-senstitive when doing text comparisons, unless you want it to be.
Thank you!

Win XP/Access 2K

 

[Dirk Goldgar]

Good morning, all.
I have a procedure that searches for an Outlook contact's
company name in Access with "SELECT ... WHERE Name = " &
OLContact.CompanyName. However, a handful of company names in
Outlook have non-breaking spaces. Access does not consider a NBS to
be equal to a normal space when doing a text comparison, so it does
not find a matching record. Can I use Replace() on
OLContact.CompanyName? If so, how do I specify
a special character to replace? If I use the ASCII code, Access
searches for the string "160," not the character #160/0xA0. Is there
a looser text comparison mode available?
Personally, I like the MySQL part of the DB; it's not even
case-senstitive when doing text comparisons, unless you want it to be.
Thank you!

Win XP/Access 2K

I don't see offhand why you couldn't have a function that replaces all
the odd characters in the name with spaces; something along these
lines:

'----- start of example code -----
Function fncFixOutlookName(OLName As String) As String

Dim strName As String

strName = Replace(OLName, Chr(160) & Chr(10), " ",
1, -1,vbBinaryCompare)
strName = Replace(strName, Chr(160), " ", 1, -1,vbBinaryCompare)
strName = Replace(strName, Chr(10), " ", 1, -1,vbBinaryCompare)

' ... do other replacements ...

fncFixOutlookName = strName

End Function
'----- end of example code -----

And then build a query along the lines of

SELECT ... WHERE [Name] = " & Chr(34) & _
fncFixOutlookName(OLContact.CompanyName) & _
Chr(34)

 

[JonOfAllTrades]

Putting the character code in Chr() is what it took. Thanks!