Can't load content containing ampersands ("&")

[#1]

I am trying to load customer names that contain ampersands ("&" symbol) and possibly other special characters. I've found that when loading customer names that contain an ampersand, all text before the "&" symbol (and the symbol itself) is not loaded.

To resolve this, I've tried using the TEXT() function, but this doesn't work. I don't want to replace the symbol with text, e.g. "and".

If anyone can help me I'd very much appreciate it!

[Anonymous]

Use "replace" twice, e.g. from '&" to 'and' on reload, and than back to '&'

[#3]

Wow, that's quick! Thanks

Followup question: if I do this, won't I also be replacing existing "and" in the names of other customers?

I wouldn't want the situation to arise where "Customer A & Co." and "Customer A and Co." would be 'merged' into a single "Customer A & Co."...

[#4 · Accepted solution]

Use some unique string like REPLACE(name, '&', 'zzzANDzzz') and then replace it back.

[Anonymous]

You're right.  I just was using your example.  In reality, you better use something less likely, e.g. "Mickey Mouse"

[#6]

Thanks both; works like a charm

In the end I had to perform the first replace in my (MS Access) staging script as Replace(Name,Chr(38), 'zzzANDzzz'), then run the next one in my QV load script Replace(Name, 'zzzANDzzz',Chr(38))

[vijetas42]

I am facing same issue for date like, Kirl & Elle and it's loading only Elle. can you help me on this. I have tried replace but not working.