Problems with SQL in vba code

[richard]

Hi

I am trying to run an append query with criteria from a select box stored in
a form. However I am getting a run time error 3075 saying there are missing
operators.
This is my first conversion of an SQL statement into code so I am lost. Any
help appreciated

Private Sub Report_Close()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Dim strAssistFMJobs As String
Dim stDocName As String
Dim ctrl As Control
Dim strSQL As String

Set db = CurrentDb()
Set ctrl = Forms!frmAssistFMDataSheetPrints.lstAssistFMJobs
Set qdf = db.QueryDefs("qrynapsworkappend")

For Each varItem In ctrl.ItemsSelected
strAssistFMJobs = strAssistFMJobs & "," & ctrl.ItemData(varItem)
Next varItem

If Len(strAssistFMJobs) = 0 Then
MsgBox "You did not select anything from the list", vbExclamation,
"Nothing to find!"
Exit Sub
End If

strAssistFMJobs = Mid(strAssistFMJobs, 2)

strSQL = "INSERT INTO tblnapsworkcheckbox ( siteref, BatchDate, BatchNumber,
Seperator )" & _
"SELECT tblnapswork.siteref, Now() AS expr2,
Nz(DMax([BatchNumber],[tblAssistFMSubJobNumbers]),0)+1 AS expr3,
tblAssistFMSubJobNumbers.Seperator " & _
"FROM tblnapswork INNER JOIN tblAssistFMSubJobNumbers ON
tblnapswork.jobnumber = tblAssistFMSubJobNumbers.JobNumber" & _
"WHERE tblAssistFMSubJobNumbers.Seperator In(" & strAssistFMJobs &
");"

qdf.SQL = strSQL

DoCmd.OpenQuery "qrynapsworkappend"


CurrentDb.Execute ("qrynapsworkupdate"), dbFailOnError
CurrentDb.Execute ("qrynapsworkdelete"), dbFailOnError


End Sub

 

[Armen Stein]

strSQL = "INSERT INTO tblnapsworkcheckbox ( siteref, BatchDate, BatchNumber,
Seperator )" & _
"SELECT tblnapswork.siteref, Now() AS expr2,
Nz(DMax([BatchNumber],[tblAssistFMSubJobNumbers]),0)+1 AS expr3,
tblAssistFMSubJobNumbers.Seperator " & _
"FROM tblnapswork INNER JOIN tblAssistFMSubJobNumbers ON
tblnapswork.jobnumber = tblAssistFMSubJobNumbers.JobNumber" & _
"WHERE tblAssistFMSubJobNumbers.Seperator In(" & strAssistFMJobs &
");"

The best way to check something like this is to set a breakpoint in
your code after the strSQL is built. Use ?strSQL in the Immediate
Window to view the string. You might immediately see the problem, or
you can copy and paste it into a new Access query to see where it
fails.

At a glance, it looks like you are missing spaces before your SELECT
and WHERE operators.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[mscertified]

Display the SQL before it runs and post it here

-Dorian