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;
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;
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With