SQL problem WHERE clause from 2 tables

[andy1968]

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

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]

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]

I agree with theDBGuy---Why? Please tell us in business terms/plain English what you are trying to achieve.

 

[andy1968]

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.

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]

Working on the assumption that the first two are numbers and the last is text then

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]

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]

SerialNumber is the only number. The others are text.

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

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]

SerialNumber is the only number. The others are text.

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

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]

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]

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.