By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Error 3021 in SQL using multiple INNER JOINS.

P: 10
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. 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)]
  3. WHERE (((tbl_Reject.[Reject_Number(PK)])=250189));
  4.  
The modified version for VBA:
Expand|Select|Wrap|Line Numbers
  1. strSQL_Edit_Record = "SELECT * FROM (((tbl_Reject INNER JOIN tbl_Serial_Number ON tbl_Reject.[Reject_Number(PK)] = tbl_Serial_Number.[Reject_number(FK)])" & _
  2. " INNER JOIN tbl_Approvals ON tbl_Reject.[Reject_Number(PK)] = tbl_Approvals.[Reject_Number(FK)])" & _
  3. " INNER JOIN tbl_Expeditor_Defect_Type ON tbl_Reject.[Reject_Number(PK)] = tbl_Expeditor_Defect_Type.[Reject_number(FK)])" & _
  4. " INNER JOIN tbl_Inventory_Reason_Code ON tbl_Reject.[Reject_Number(PK)] = tbl_Inventory_Reason_Code.[Reject_number(FK)]" & _
  5. " WHERE (((tbl_Reject.[Reject_Number(PK)])=" & lngEditCriteria & "));"
  6.  
Any ideas? Thanks in advance.

Steve
Jan 20 '09 #1
Share this Question
Share on Google+
4 Replies


DonRayner
Expert 100+
P: 489
Is there a posibility that your lngEditCriteria variable is null?
Jan 21 '09 #2

P: 10
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
Jan 21 '09 #3

NeoPa
Expert Mod 15k+
P: 31,418
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!
Jan 21 '09 #4

P: 10
I found it. It was actually in my code.

Original:
Expand|Select|Wrap|Line Numbers
  1. Do Until a = iQty
  2.     strErrSect = " [Fill_Record ME-Manufacturing Engineer D2] "
  3.     BigLine = BigLine & "Serial #: " & !Part_serial_number & c5SP & "Date Code: " & !Date_code & cDCF
  4.     MsgBox "BIGLINE [" & a & "]: " & BigLine, , strErrSect
  5.     a = a + 1
  6.     rs.MoveNext (This moved to a record that did not exist once the count was reached)
  7. Loop
Changed to:
Expand|Select|Wrap|Line Numbers
  1. Do Until a = iQty
  2.     strErrSect = " [Fill_Record ME-Manufacturing Engineer D2] "
  3.     BigLine = BigLine & "Serial #: " & !Part_serial_number & c5SP & "Date Code: " & !Date_code & cDCF
  4.     MsgBox "BIGLINE [" & a & "]: " & BigLine, , strErrSect
  5.     a = a + 1
  6.     If a <> iQty Then
  7.     rs.MoveNext
  8.     End If
  9. Loop
Thanks for all of your help.

Steve
Jan 21 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.