SQL problem WHERE clause from 2 tables (1 Viewer)

andy1968

Registered User.
Local time
Yesterday, 23:58
Joined
May 9, 2018
Messages
131
I'm been struggling with this SQL statement for several days.

I want to insert values from 2 tables into another.

I get a missing operator error on the WHERE statement
Code:
Private Sub cmdInsertDefaultReviewers_Click()

Dim strSQL, strCN As String    'SQL statement.
Dim lngID As Long       'Primary key value of the new record.
Dim NewDate, NewDueDate As Date
lngID = Me.SerialNumber
strCN = Me.ContractorNumber

NewDate = Date
NewDueDate = DateAdd("d", 7, NewDate)
            If Len(Me.DefaultReviewerType) > 0 Then
              
               strSQL = "INSERT INTO tblSubmittalSuplementalReviewer ( SerialNumber , ContractorNumber, Reviewer)" & _
                        "SELECT " & lngID & " As SerialNumber , " & strCN & " As ContractorNumber, Reviewer " & _
                        "FROM tblSubmittalReviewList, tblSubmittalInfo " & _
                        "WHERE tblSubmittalReviewList.SerialNumber = " & lngID & " AND  tblSubmittalReviewList.ContractorNumber = " & strCN & " AND tblSubmittalInfo.SubmitalType = " & Me.DefaultReviewerType & ";"
               DBEngine(0)(0).Execute strSQL, dbFailOnError
              
                
            Else
                MsgBox "No Default Reviewer Selected."
            End If
Me.frmSubmittalSupplementalReviewer.Requery
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:58
Joined
Oct 29, 2018
Messages
21,358
Hi. First impression: Why are you storing duplicate data in multiple tables? Next question: Have you tried doing Debug.Print strSQL and then pasting it into the query designer to see what the problem was? Just curious...
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:58
Joined
Jan 23, 2006
Messages
15,364
I agree with theDBGuy---Why? Please tell us in business terms/plain English what you are trying to achieve.
 

andy1968

Registered User.
Local time
Yesterday, 23:58
Joined
May 9, 2018
Messages
131
Good questions.

tblSubmittalReviewList contains a listing of "Default" reviewers for specific types of Submittals for a construction project.

For example, all of the architecture submittals would go to the Architect and the owner.

I want the user to pick the type of submittal from a combo box, and have Access add the related reviewers entered into a table to store the reviewers for a particular submittal.

Put the strSQL into the query designer, got the same error.

This is the SQL from the query designer that works. When I put this into VBA, I get a to few parameters errors. Says they are looking for 3.

Code:
INSERT INTO tblSubmittalSuplementalReviewer ( Reviewer, SerialNumber, ContractorNumber )
SELECT tblSubmittalReviewList.Reviewer, tblSubmittalInfo.SerialNumber, tblSubmittalInfo.ContractorNumber
FROM tblSubmittalReviewList, tblSubmittalInfo
WHERE (((tblSubmittalInfo.SerialNumber)=[Forms]![frmSubmittalEntry]![SerialNumber]) AND ((tblSubmittalInfo.ContractorNumber)=[Forms]![frmSubmittalEntry]![ContractorNumber]) AND ((tblSubmittalReviewList.SubmitalType)=[Forms]![frmSubmittalEntry]![DefaultReviewerType]));
 

isladogs

MVP / VIP
Local time
Today, 06:58
Joined
Jan 14, 2017
Messages
18,186
Working on the assumption that the first two are numbers and the last is text then
Code:
INSERT INTO tblSubmittalSuplementalReviewer ( Reviewer, SerialNumber, ContractorNumber )
SELECT tblSubmittalReviewList.Reviewer, tblSubmittalInfo.SerialNumber, tblSubmittalInfo.ContractorNumber
FROM tblSubmittalReviewList, tblSubmittalInfo
WHERE (((tblSubmittalInfo.SerialNumber)=" & [Forms]![frmSubmittalEntry]![SerialNumber]) & "
AND ((tblSubmittalInfo.ContractorNumber)=" & [Forms]![frmSubmittalEntry]![ContractorNumber]) & "
AND ((tblSubmittalReviewList.SubmitalType)='" & [Forms]![frmSubmittalEntry]![DefaultReviewerType])) & "'";

Similarly in your First post, if SubmittalType is a text field, it should have single quotes as for DefaultReviewerType above
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:58
Joined
Oct 29, 2018
Messages
21,358
Put the strSQL into the query designer, got the same error.
I expect you would, but it should have given you a clue as to what the problem was, didn't it? Can you show us the result of the Debug.Print strSQL?
 

andy1968

Registered User.
Local time
Yesterday, 23:58
Joined
May 9, 2018
Messages
131
SerialNumber is the only number. The others are text.

Here's the Debug.Print strSQL after I made changes suggested by isladogs:

Code:
INSERT INTO tblSubmittalSuplementalReviewer ( Reviewer, SerialNumber, ContractorNumber )SELECT tblSubmittalReviewList.Reviewer, tblSubmittalInfo.SerialNumber, tblSubmittalInfo.ContractorNumber  FROM tblSubmittalReviewList, tblSubmittalInfo  WHERE (((tblSubmittalInfo.SerialNumber)=1933 AND ((tblSubmittalInfo.ContractorNumber)= '132' AND ((tblSubmittalReviewList.SubmitalType)= 'Expressway Traffic, etc'"

Continuing to play around with this.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:58
Joined
Oct 29, 2018
Messages
21,358
SerialNumber is the only number. The others are text.

Here's the Debug.Print strSQL after I made changes suggested by isladogs:

Code:
INSERT INTO tblSubmittalSuplementalReviewer ( Reviewer, SerialNumber, ContractorNumber )SELECT tblSubmittalReviewList.Reviewer, tblSubmittalInfo.SerialNumber, tblSubmittalInfo.ContractorNumber  FROM tblSubmittalReviewList, tblSubmittalInfo  WHERE (((tblSubmittalInfo.SerialNumber)=1933 AND ((tblSubmittalInfo.ContractorNumber)= '132' AND ((tblSubmittalReviewList.SubmitalType)= 'Expressway Traffic, etc'"

Continuing to play around with this.
Hi. Two things I noticed with your SQL.

1. You are using a Cartesian Query to add records. Is that intentional?

2. There's a "etc" at the end of your SQL. Is that correct? The error message says there's something wrong in the WHERE clause, right? If so, that could be it. Otherwise, if you're trying to spare us from reading a long WHERE clause, then I'm afraid we won't be able to tell you what's wrong with it, if we can't see the whole thing.
 

andy1968

Registered User.
Local time
Yesterday, 23:58
Joined
May 9, 2018
Messages
131
Made a work around.

Used a Docmd.OpenQuery.

Wish I thought of this before.

Not as elegant, but works like a charm.

Thanks for sticking with me on this.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:58
Joined
Oct 29, 2018
Messages
21,358
Made a work around.

Used a Docmd.OpenQuery.

Wish I thought of this before.

Not as elegant, but works like a charm.

Thanks for sticking with me on this.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom