MS-Access查询中的语法错误(缺少运算符) [英] Syntax error (missing operator) in MS-Access Query

问题描述

以下查询给了我"(missing operator)"语法错误.所需的输出是来自表[dbo_tbl*]和视图[vw_*]的数据的组合.我使用过的所有键都存在.有什么想法吗?

The following query is giving me the "(missing operator)" syntax error. The desired output is a combination of data from tables [dbo_tbl*] and views [vw_*]. All of the keys I've used exist. Any ideas?

SELECT dbo_tbl_BOD.fpartno AS PartNumber,
      dbo_tbl_BOD.frev AS RevisionIssue,
      vw_DOCSwType.DocID,
      vw_DRHRelfilter.Rev,
      vw_DOCSwType.DocTypeDesc,
      vw_DOCSwType.DocDesc,
      vw_DOCSwType.DwgNoLegacy,
      vw_DOCSwType.FileLocationOld,
      vw_DRHRelfilter.DateCreated,
      vw_DOCSwType.CreatedBy,
      vw_DRHRelfilter.Rel,
      vw_DRHRelfilter.RelLink
FROM dbo_tbl_BOD
LEFT JOIN vw_DRHRelfilter
      ON ((dbo_tbl_BOD.DocID=vw_DRHRelfilter.DocID)
     AND (dbo_tbl_BOD.frev=vw_DRHRelfilter.Rev))
 LEFT JOIN vw_DOCSwType
      ON (dbo_tbl_BOD.DocID=vw_DOCSwType.DocID)
ORDER BY PartNumber;

推荐答案

使用Access，每个联接都需要括号，例如:

With Access, each join needs parentheses, say:

SELECT dbo_tbl_BOD.fpartno AS PartNumber,
dbo_tbl_BOD.frev AS RevisionIssue,
vw_DOCSwType.DocID,
vw_DRHRelfilter.Rev,
vw_DOCSwType.DocTypeDesc,
vw_DOCSwType.DocDesc,
vw_DOCSwType.DwgNoLegacy,
vw_DOCSwType.FileLocationOld,
vw_DRHRelfilter.DateCreated,
vw_DOCSwType.CreatedBy,
vw_DRHRelfilter.Rel,
vw_DRHRelfilter.RelLink
FROM (dbo_tbl_BOD
LEFT JOIN vw_DRHRelfilter
ON dbo_tbl_BOD.DocID=vw_DRHRelfilter.DocID
AND dbo_tbl_BOD.frev=vw_DRHRelfilter.Rev)
LEFT JOIN
vw_DOCSwType
ON dbo_tbl_BOD.DocID=vw_DOCSwType.DocID
ORDER BY PartNumber;

如果您拥有MS Access的副本，则可以使用查询设计窗口来建立联接，从而轻松地看到所需的括号.

If you have a copy of MS Access, you can easily see the required parentheses by using the query design window to build your joins.

您可以使用VBA检查字段类型，例如:

You can use VBA to check field types, for example:

Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set rs = CurrentDb.OpenRecordset("query1")
For Each fld In rs.Fields
    Debug.Print fld.SourceField, fld.SourceTable, _
        DLookup("SQLName", "DataTypeEnum", "DataValue=" & fld.Type)
Next

表DataTypeEnum包含对fld.type返回的数值的文本描述.

The table DataTypeEnum contains text descriptions for the numeric values returned by fld.type.

DataValue  SQLName
1   Boolean
2   Byte
3   Integer
4   Long
5   Currency
6   Single
7   Double
8   Date
9   Binary
10  Text
11  LongBinary
12  Memo
15  GUID
16  BigInt
17  VarBinary
18  Char
19  Numeric
20  Decimal
21  Float
22  Time
23  TimeStamp

这篇关于MS-Access查询中的语法错误(缺少运算符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！