Cannot Find Rows With % character in Text Fields with Access SQL
-
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s. I have a table named "Foo", with two rows: Type as text, SubType as text, with two rows of values: "Sales Tax (%)", "NY" "Sales Tax (%)", PA" When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)" But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned Even if I omit the parenthesis around the % sign, it does not find the rows. Is there something odd I am doing wrong, perhaps specific to Access SQL?
-
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s. I have a table named "Foo", with two rows: Type as text, SubType as text, with two rows of values: "Sales Tax (%)", "NY" "Sales Tax (%)", PA" When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)" But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned Even if I omit the parenthesis around the % sign, it does not find the rows. Is there something odd I am doing wrong, perhaps specific to Access SQL?
<edit>move on, nothing to see here except the proofs of to little sleep.</edit> Access isn't SQLServer. You're not supposed to use brackets either. I would strongly recommend renaming the offending fields. More info here[^]. I especially like this part: "However, if you do use the special characters, you may experience unexpected errors."
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
<edit>move on, nothing to see here except the proofs of to little sleep.</edit> Access isn't SQLServer. You're not supposed to use brackets either. I would strongly recommend renaming the offending fields. More info here[^]. I especially like this part: "However, if you do use the special characters, you may experience unexpected errors."
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
I am not sure I understand. The % character does not appear in any field names, or table names. The data type of both fields, named "Type" and "SubType" are text, and that text data can contain a % character as part of whatever text the user chooses to enter into the field, along with other special characters. (I have an application where I prompt the user to enter a text value for the Type and SubType fields, and I save that text into those fields as a new row. The problem I have is that the user can enter a % as part of their user-entered text, and I need to be able to search on text field data for a % character they may have entered; I can put brackets around all other special characters when building my query and they are found fine, but the % just doesn't work for some reason when used in an = query.
-
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s. I have a table named "Foo", with two rows: Type as text, SubType as text, with two rows of values: "Sales Tax (%)", "NY" "Sales Tax (%)", PA" When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)" But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned Even if I omit the parenthesis around the % sign, it does not find the rows. Is there something odd I am doing wrong, perhaps specific to Access SQL?
JohnBlocker wrote:
Even if I omit the parenthesis around the % sign, it does not find the rows.
So this:
SELECT * From Foo WHERE Type = 'Sales Tax (%)'
returns nothing?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I am not sure I understand. The % character does not appear in any field names, or table names. The data type of both fields, named "Type" and "SubType" are text, and that text data can contain a % character as part of whatever text the user chooses to enter into the field, along with other special characters. (I have an application where I prompt the user to enter a text value for the Type and SubType fields, and I save that text into those fields as a new row. The problem I have is that the user can enter a % as part of their user-entered text, and I need to be able to search on text field data for a % character they may have entered; I can put brackets around all other special characters when building my query and they are found fine, but the % just doesn't work for some reason when used in an = query.
My bad, I think my brain went to sleep before the rest of me did.
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s. I have a table named "Foo", with two rows: Type as text, SubType as text, with two rows of values: "Sales Tax (%)", "NY" "Sales Tax (%)", PA" When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)" But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned Even if I omit the parenthesis around the % sign, it does not find the rows. Is there something odd I am doing wrong, perhaps specific to Access SQL?
-
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s. I have a table named "Foo", with two rows: Type as text, SubType as text, with two rows of values: "Sales Tax (%)", "NY" "Sales Tax (%)", PA" When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)" But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned Even if I omit the parenthesis around the % sign, it does not find the rows. Is there something odd I am doing wrong, perhaps specific to Access SQL?
This could be an odd question, but, you tried
Quote:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
Cause, when your using '=' command, means that is HAS to be egual.
