By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Escape % from VBA SQL String

100+
P: 124
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.
May 25 '17 #1

✓ answered by NeoPa

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 match 5% use '5[%]'.

For quote characters simply double them up. In a SQL string the string Donald O'Malley would be written as 'Donald O''Malley'. In a VBA string 5' 3" would be written as "5' 3""".

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,409
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 match 5% use '5[%]'.

For quote characters simply double them up. In a SQL string the string Donald O'Malley would be written as 'Donald O''Malley'. In a VBA string 5' 3" would be written as "5' 3""".
May 26 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.