Hi

I have a 'search by surname' box on a form which applies a simple filter, but if a user put's an apostrophe in, the form returns a runtime error 3075 - Syntax Error (missing operator) in experssion'Surname like '*o'malley*'

From a bit of googling I've found out I need to replace the single apostrophe with a double apostrophe (and in fact if I add a double apostrophe to the search box it works fine) but I'm struggling with the syntax of the replace.

My search button has this syntax:


If anyone could help with where and how the replace command is used I'd be realyl grateful

Thanks

Well --- I actually don't think its a personal thing (IMG:style_emoticons/default/smile.gif) . I am not trying to be combative, but lets take a look a some things. First, you presented custom code and proposed a solution by using the custom code and that solution involved one function call ...



Unfortunatly, you suggestion will not work --- remember how I indicated that your code assumes the use of the delimiter being used? Well, in yours you just assume no delimiter is use at all and you depend on the UDF to return the delimiter, so to properly use the function you supplied and to do a wild card search, you would have to use:



So, now a future caretaker of the code must learn and remember all that meta data on the proper usage of your UDF to simply build a string, that honestly a programmer should know how to build.

But if you use the inherent Replace() function, it puts the developer in control as to how they want to build the string they want for the purpose they need ...



Replace() is inherent to VBA, no extra code to implement and maintain. The solution is still builds the string required for the solution with a single function call, so really no difference in complexity the string building expression. With Replace(), there is no extra VBA code to maintain and it is very clear what is going to happen, where as with your FixMyString() function, you have to remember the caveats and adjust accordingly, and if you don't remember them, you have a longer troubleshooting path in front of you when issues arise in the final resultant string.

----

Again, as I said in the start -- I am not trying to be combative or anything of the sort, just showing why creating a function to do things such as what FixMyString() is not an approach I would encourage for someone to adopt, or even continue to maintain. Plus explain why the reasons for discouraging it go beyond that of personal preference. (IMG:style_emoticons/default/thumbup.gif)