Error 3021 in SQL using multiple INNER JOINS.

[AXESMI59]

I created a query in the Access Query Builder that I modified to use with VBA Code so I could reuse it for any record I choose. The one that I created with the Access Query builder works beautifully with the hardcoded criteria. The VBA version with my criteria being set by a variable returns Error 3021.
I am using a DAO Recordset and all I want to do is retrieve the data to place into a report.

The Original made in the Access Query Builder:

SELECT *
FROM (((tbl_Reject INNER JOIN tbl_Serial_Number ON tbl_Reject.[Reject_Number(PK)] = tbl_Serial_Number.[Reject_number(FK)]) INNER JOIN tbl_Approvals ON tbl_Reject.[Reject_Number(PK)] = tbl_Approvals.[Reject_Number(FK)]) INNER JOIN tbl_Expeditor_Defect_Type ON tbl_Reject.[Reject_Number(PK)] = tbl_Expeditor_Defect_Type.[Reject_number(FK)]) INNER JOIN tbl_Inventory_Reason_Code ON tbl_Reject.[Reject_Number(PK)] = tbl_Inventory_Reason_Code.[Reject_number(FK)]
WHERE (((tbl_Reject.[Reject_Number(PK)])=250189));
 

The modified version for VBA:

strSQL_Edit_Record = "SELECT * FROM (((tbl_Reject INNER JOIN tbl_Serial_Number ON tbl_Reject.[Reject_Number(PK)] = tbl_Serial_Number.[Reject_number(FK)])" & _
" INNER JOIN tbl_Approvals ON tbl_Reject.[Reject_Number(PK)] = tbl_Approvals.[Reject_Number(FK)])" & _
" INNER JOIN tbl_Expeditor_Defect_Type ON tbl_Reject.[Reject_Number(PK)] = tbl_Expeditor_Defect_Type.[Reject_number(FK)])" & _
" INNER JOIN tbl_Inventory_Reason_Code ON tbl_Reject.[Reject_Number(PK)] = tbl_Inventory_Reason_Code.[Reject_number(FK)]" & _
" WHERE (((tbl_Reject.[Reject_Number(PK)])=" & lngEditCriteria & "));"
 

Any ideas? Thanks in advance.

Steve

[DonRayner]

Is there a posibility that your lngEditCriteria variable is null?

[AXESMI59]

No, I used the same data in the VBA one that I had hardcoded in the Access Builder. I have a message box that shows the SQL for debugging and it is there. This has me puzzled.

Steve

[NeoPa]

Can you please provide the error message as well as the code used to execute your SQL.

I can confirm that the SQL resolves to essentially the same SQL as that provided by the Query Builder.

Also, assuming [lngEditCriteria] is Dimmed as Long (which I assume) then it won't contain a null or any invalid value (It may not match a record - but won't be invalid).

Welcome to Bytes!

[AXESMI59]

I found it. It was actually in my code.

Original:

Do Until a = iQty
    strErrSect = " [Fill_Record ME-Manufacturing Engineer D2] "
    BigLine = BigLine & "Serial #: " & !Part_serial_number & c5SP & "Date Code: " & !Date_code & cDCF
    MsgBox "BIGLINE [" & a & "]: " & BigLine, , strErrSect
    a = a + 1
    rs.MoveNext (This moved to a record that did not exist once the count was reached)
Loop

Changed to:

Do Until a = iQty
    strErrSect = " [Fill_Record ME-Manufacturing Engineer D2] "
    BigLine = BigLine & "Serial #: " & !Part_serial_number & c5SP & "Date Code: " & !Date_code & cDCF
    MsgBox "BIGLINE [" & a & "]: " & BigLine, , strErrSect
    a = a + 1
    If a <> iQty Then
    rs.MoveNext
    End If
Loop

Thanks for all of your help.

Steve