docmd.runSQL code help - INSERT INTO with multiple WHERE clauses (1 Viewer)

J

JeffBarker

Registered User.
Local time
Today, 12:32
Joined
Dec 7, 2010
Messages
130
Hi guys,

I'm trying to run an INSERT INTO statement via docmd.RunSQL, and I'm stumped - I keep getting the following syntax error:

Syntax error (missing operator) in query expression '(((tblHistory.HistoryID) = 42848) AND ((LookUpAllocater.Publication) = AHCP) AND ((LookUpAllocater.Issue) = Summer 2015) AND ((LookUpPageSize.Size) = Special))'.

The code I'm running is:

Code: 
vSQL = "INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

I'm convinced that I'm literally a couple of character changes away from solving this, but I'll admit that I am totally stumped!

If anyone can tell me where I'm going wrong, and how to solve it, I would be most greatful! :)

Cheers,

Jeff.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 07:32
Joined
Jan 23, 2006
Messages
15,379
It appears that some of your variables are text/string data type and as such must be enclosed in quotes " text "
seems AHCP and Summer 2015 and Special suffer from lack of quotes

Now -includes data and time is that what you need?

A good debugging technique is to do a debug.print vSql to see what Access "understands/renders".

From which table are Artworkstatus,Publication and Issue?
You can test a SELECT query in order to get a better understanding of syntax. Select is READ ONLY so won't affect your data. Use the select until you get the syntax correct, then convert to INSERT.

Code: 
SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

Made up example showing use of quotes
Code: 
mySQL = "SELECT FirstName, LastName From tblPersonInfo" & _
            " WHERE City = '" & Forms!MyForm!txtCityName & "';"
 
RainLover

RainLover

VIP From a land downunder
Local time
Today, 21:32
Joined
Jan 5, 2009
Messages
5,041
Try creating a query using the query grid.

Once you have that you can do a copy paste of the query from the SQL view.
 
J

JeffBarker

Registered User.
Local time
Today, 12:32
Joined
Dec 7, 2010
Messages
130
jdraw said:
It appears that some of your variables are text/string data type and as such must be enclosed in quotes " text "
seems AHCP and Summer 2015 and Special suffer from lack of quotes

Now -includes data and time is that what you need?

A good debugging technique is to do a debug.print vSql to see what Access "understands/renders".

From which table are Artworkstatus,Publication and Issue?
You can test a SELECT query in order to get a better understanding of syntax. Select is READ ONLY so won't affect your data. Use the select until you get the syntax correct, then convert to INSERT.

Code: 
SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

Made up example showing use of quotes
Code: 
mySQL = "SELECT FirstName, LastName From tblPersonInfo" & _
            " WHERE City = '" & Forms!MyForm!txtCityName & "';"
Click to expand...

Hey jdraw, thanks for the response!

I have been playing around with a similar INSERT INTO statement elsewhere in my database, and that works, so I've copied the same format and set the following variables to read fields from the form that I'm running the code from.

These variables are HisID, ArtStat, Tit, Ish and Size, all as strings.

HisID is the record unique identifier, ArtStat is based on a combobox (which runs the INSERT INTO statement on its AfterUpdate Event), Tit, Ish and Size are all text boxes.

Here's that new code:

Code: 
Private Sub cboArtworkStatus_AfterUpdate()
On Error GoTo Err_cboArtworkStatus_AfterUpdate

    Dim HisID, ArtStat, Tit, Ish, Size, vSQL As String
    
    HisID = Me.txtOrderRef
    ArtStat = Me.cboArtworkStatus
    Tit = Me.txtTitle
    Ish = Me.txtIssue
    Size = Me.txtSize

    vSQL = " INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblAllocation.HistoryID, Now() AS [Date], fGetUserName() AS UN, 'Artwork Status changed to ' & ArtStat & ' on ' & Tit & ' - ' & Ish & ' - ' & Size AS Det " _
    & " FROM tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID " _
    & " WHERE (((tblAllocation.HistoryID)= " & HisID & "))"

    DoCmd.SetWarnings False

    DoCmd.RunSQL vSQL

    DoCmd.SetWarnings True

Exit_cboArtworkStatus_AfterUpdate:
    Exit Sub
    
Err_cboArtworkStatus_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cboArtworkStatus_AfterUpdate

End Sub

When I update cboArtworkStatus, I get the Enter Parameter Value box for ArtStat, then Tit, then Ish, then Size - even though (when I step through the code), these variables are reading the values of their respective combo/text boxes from the form.

Do you have any suggestions on what could be going wrong, and how to fix this, please?
 
J

JeffBarker

Registered User.
Local time
Today, 12:32
Joined
Dec 7, 2010
Messages
130
RainLover said:
Try creating a query using the query grid.

Once you have that you can do a copy paste of the query from the SQL view.
Click to expand...

Hi RainLover, thanks for the response - this is what I'd done originally, and was having trouble with. Probably should have mentioned that to start with! :)
 
RainLover

RainLover

VIP From a land downunder
Local time
Today, 21:32
Joined
Jan 5, 2009
Messages
5,041
JeffBarker said:
Hi RainLover, thanks for the response - this is what I'd done originally, and was having trouble with. Probably should have mentioned that to start with! :)
Click to expand...

Have you gone back and found the fault?

Hint. Start small get that working before adding something else. One step at a time.

BTW Turning Warnings off. Why have you done that. If there is an error you need to see what it is, not hide it.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 07:32
Joined
Jan 23, 2006
Messages
15,379
I agree with Rainlover. Get something basic working, then add additional functionality, slowly.
Suppressing the warning is basically telling Access not to help you resolve the error???

I would also include a debugging statement before trying to execcute/run that sql.
Code: 
Debug.Print vSQL

And, to repeat, I would work with a SELECT query to get the SQL syntax correct and to get a working query. (You can use the query grid to do a SELECT, then convert to an INSERT for final sql).
 
RainLover

RainLover

VIP From a land downunder
Local time
Today, 21:32
Joined
Jan 5, 2009
Messages
5,041
Jeff

What's happening. Did you solve the problem.

Don't forget that we are here to help
 
J

JeffBarker

Registered User.
Local time
Today, 12:32
Joined
Dec 7, 2010
Messages
130
RainLover said:
Jeff

What's happening. Did you solve the problem.

Don't forget that we are here to help
Click to expand...

Hi Rain,

Thanks for checking up - we've been moving offices for the past week, and today is the first day I've sat back down at my desk!

I have a ton of reports to catch up on, but this project is my next priority so once I've caught up on everything else I'll pick this back up again and feed back!

Cheers,

Jeff.
 
M

marlan

Registered User.
Local time
Today, 14:32
Joined
Jan 19, 2010
Messages
409
Hi,
I your SELECT and FROM section you use JET SQL parameters and field names, in the case the JET interpeter knows what data type is each parameter.
In the WHERE section, parameters are VBA, for the JET interpeter, it makes no diference if the text is hard coded or a variable - it is sent for interpetation. And expects to receive text in quotes.
you can see in the error message: ((LookUpAllocater.Publication) = AHCP) AND ((LookUpAllocater.Issue) = Summer 2015)
'AHCP' and 'Summer 2015' are Text values, and should be in quotes:

Code: 
" WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = '" & vTitle & "') AND ((LookUpAllocater.Issue) = '" & vIssue & "') AND ((LookUpPageSize.Size) = '" & vSize & "')) "
ATB
 
M

marlan

Registered User.
Local time
Today, 14:32
Joined
Jan 19, 2010
Messages
409
Hi Jeff,

Was I of any help?
Was I understood?...
 
J

JeffBarker

Registered User.
Local time
Today, 12:32
Joined
Dec 7, 2010
Messages
130
Hi guys - thanks again for all your suggestions and assistance, and apologies for the very late reply...been kinda busy here again, so dropped off this project!

Finally manage to solve the problem, by using the following:

Code: 
    vSQL = " INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblAllocation.HistoryID, Now(), fGetUserName(), 'Artwork Status changed to " & vArtStat & " on " & vTitle & " - " & vIssue & " - " & vSize & "'" _
    & " FROM tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID " _
    & " WHERE tblAllocation.HistoryID = " & vHisID & " AND tblAllocation.AllocaterID = " & vAllocID

So thanks again for all your help!! :)
 
You must log in or register to reply here.

Similar threads

J
Weird Type Mismatch error, only happens when executing code from On Click event.
Replies
6
Views
1,965
JeffBarker
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom