How does one escape this % character from a VBA string used for searching in SQL? I get an error any time a SQL string with % is used for searching. I didn't even realize it was a reserved character in MS Access. I thought it was used in SQL Server as the Access equivalent of *, but apparently it is also used in Access. Also, what is the best way to escape single quotes within a string. Thanks in advance for responses.
-
-
Hi BikeToWork.
Actually, I believe the percent (%) can be used in Access as well as SQL Server SQL but only if the database is set to use ANSI-92 (File | Options | Object Designers | Query Design | SQL Server Compaitble Syntax (ANSI-92)). See ANSI Standards in String Comparisons for more on that.
When in use and not desired as a wildcard then any of these special characters (except quotes) can be matched if included within brackets ([]). So, to match5%use '5[%]'.
For quote characters simply double them up. In a SQL string the stringDonald O'Malleywould be written as 'Donald O''Malley'. In a VBA string5' 3"would be written as "5' 3""".
Comment