为什么我的LIKE查询仅在Microsoft Access 2013中不返回任何记录? [英] Why is my LIKE query not returning any records in Microsoft Access 2013 only?
问题描述
我的SQL查询如下:
SELECT * FROM Suppliers
WHERE SupplierName
LIKE 's%';
SELECT * FROM Suppliers
WHERE SupplierName
LIKE 's%';
当我在W3 School的TryIt编辑器(v1.2)上提交此查询时( http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_like )，它会按预期返回许多记录，其中SupplierName以字母"S"开头.
When I submit this query on W3 School's TryIt Editor (v1.2) (http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_like), it returns a number of records where the SupplierName begins with the letter 'S', as expected.
但是，当我在Access 2013中对自己的数据库运行此查询时，即使我的表名和字段名与W3的示例相同，它也不会返回任何记录.
However, when I run this query on my own database in Access 2013, it doesn't return any records, even though my table name and field name are identical to W3's sample.
例如，不使用"LIKE"的查询似乎可以正常工作，
Queries not using 'LIKE' seem to work ok, for example:
SELECT * FROM Suppliers
WHERE SupplierName="Part Supplier, Inc.";
SELECT * FROM Suppliers
WHERE SupplierName="Part Supplier, Inc.";
返回适当的记录.只有使用"LIKE"时，Access才根本不返回任何记录.
returns the appropriate record(s). It is only when 'LIKE' is used that Access returns no records at all.
我的问题是，为什么LIKE查询不返回任何记录?后端数据源是一个Microsoft JET数据库(这是一个相对较小的数据库-不需要完整的SQL Server)，但是我认为这不会有所作为.执行查询时，Access不会抱怨语法或抛出错误.
My question is, is there any reason why LIKE queries don't return any records? The back-end data source is a Microsoft JET database (this is a relatively small database - no need for full SQL Server), but I don't think this should make a difference. Access doesn't complain about syntax or throw an error when I execute the query.
推荐答案
来自 http ://technet.microsoft.com/zh-CN/library/cc966377.aspx :
Microsoft Jet使用Like运算符使用部分匹配(或通配符")字符，这些字符与大多数SQL方言中使用的字符不同.星号(*)字符与零个或多个字符匹配，并且等效于ANSI SQL中的百分比(％)字符.其他Microsoft Jet部分匹配字符是问号(?)(与单个字段中的任何字符匹配)和数字符号(#)，与单个字段中的任何数字匹配.
Microsoft Jet uses partial match (or "wildcard") characters with the Like operator that are different from those used in most SQL dialects. The asterisk (*) character matches zero or more characters and is equivalent to the percent (%) character in ANSI SQL. The other Microsoft Jet partial match characters are the question mark (?), which matches any character in a single field, and the number sign (#), which matches any digit in a single field.
查询的修改方式应如下:
The query should be modified like so:
SELECT * FROM Suppliers WHERE SupplierName LIKE 's*';
这篇关于为什么我的LIKE查询仅在Microsoft Access 2013中不返回任何记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
