Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Use some unique string like REPLACE(name, '&', 'zzzANDzzz') and then replace it back.
Use "replace" twice, e.g. from '&" to 'and' on reload, and than back to '&'
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."...
Use some unique string like REPLACE(name, '&', 'zzzANDzzz') and then replace it back.
You're right. I just was using your example. In reality, you better use something less likely, e.g. "Mickey Mouse"
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))
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.