473,888 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error 3021 in SQL using multiple INNER JOINS.

10 New Member
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
4 3357
DonRayner
489 Recognized Expert Contributor
Is there a posibility that your lngEditCriteria variable is null?
Jan 21 '09 #2
AXESMI59
10 New Member
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
32,584 Recognized Expert Moderator MVP
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
AXESMI59
10 New Member
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

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

Similar topics

5
4343
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one child table (StudentProgress). The course progress records how a student progresses on a course. I have one course (History) and one student called Maya. I now want to record her grade (64). If I do this in Access using a form, then the form...
9
10893
by: Robert Wing | last post by:
I support an MS Access application in which errors are trapped using the On Error statement. Just recently, the users of this system have experienced run-time error number 3021 on a random basis. My biggest problem right now is that the error description associated with this error is Application-defined or object-defined error. It has always been my understanding that these error numbers would not be used by Microsoft and were available...
2
4545
by: Colleyville Alan | last post by:
I want to extract some records that are common to three tables, but not contained in the fourth. Following what I have see in the archives and also trying the unmatched records query wizard, if I only use one of those three tables and use a LEFT JOIN to the fourth and set the target field in the fourth table to null, I get the unmatched records. However, if I link the three tables together and then want to exclude records in the fourth...
2
21977
by: Polly | last post by:
I'm trying to write the results of a query, a name, ID number, and date out to a notepad .txt file to print on a "legacy" printer. I get the output from the first 2 "write" lines over the course of the recordset, but get the 3021 error for the third line, on the last record in the recordset. (I did not include the input boxes code for the dates range because
6
9327
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: tblEmployees empId -- EmpName -- EmpRole -- EmpManager -------....------------.... ---------....--------------- 1........ dan yella..........1..........2
4
2155
by: Sascha.Moellering | last post by:
Hi, the following SQL-Statement does not compile, I receive SQL0338N as an error. The erroneous parts of the statement are the left-outer-Joins, but I don't know how to solve the problem, any hints? Thanks in advance, Sascha SELECT 'LST' AS solve_name,
2
19730
by: narendra vuradi | last post by:
Hi I have a requirement where in i haev to convert the SQL from Oracle to the one which will run on the SQL server. in the Oracle Query i am doing multiple joins, between some 13 tables. and some of these joins are inner joins and some are Left outer joins. table1 inner joined with table 2 table2 inner join with table3 table2 inner join with table4 table2 left join with table5
1
1761
by: rando1000 | last post by:
I've got a query from SQL Server that I'm trying to carry over to Access. The Query uses 3 tables with two INNER JOINS, and each of the INNER JOINS has two operators, roughly like this: Select Table1.Field1, Table1.Field2 FROM Table1 INNER JOIN Table2 ON Table2.Key1 = Table1.Key1 AND Table2.Key2 = Table1.Key2 AND Table2.Key3 = Table1.Key3 INNER JOIN Table3 ON Table3.Key1 = Table2.Key1 AND Table3.Key3 = Table2.Key3 AND...
3
2283
by: modernshoggoth | last post by:
G'day all, I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations). tblFlightPlans contains the txt-column 'TripSummary', which is what I'm trying to update, as well as ID numbers for the origin, destination, and plane carrier (eg, Qantas). The SQL code below successfully joins tblLocations and tblFlightPlans and updates TripSummary...
0
9802
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10780
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5825
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6015
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4248
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.