SQL statement works in query, but on command button

[fedupwithaccess]

Hi,

I currently have a date and time picker for which when the user selects a date and presses ok, data for that particular date is extracted from a linked server table and appended to a local table in my database. When I specify the SQL in my code this works fine within queries, but when I list it as an SQL statement within VBA I am receiving the error message:


Run-time error '3067'
Query Input must contain at least one query or table.


The other strange thing is that I have virtually identical code behind another command button which works perfectly fine.



My code is.

Private Sub OK_Click()

Dim rs As New ADODB.Recordset
Dim sql1, Y As String

'DoCmd.Hourglass True
Y = Format(Me.IDatePicker, "dd/mm/yyyy")
rs.Open ("Select [date] from [qainputnumbers]"), CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While Not (rs.EOF)
If Format(rs("date"), "dd/mm/yyyy") = Y Then X = 1
rs.MoveNext
Loop
If X = 1 Then
'DoCmd.Hourglass False
MsgBox "Date already uploaded", vbCritical, "Duplicate Date Warning"
Exit Sub
End If
DoCmd.OpenForm "Processing"
DoCmd.SetWarnings False

sql1 = "INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput )" & _
"SELECT Format([dbo_aut_Invoice.Inputdate],'dd/mm/yyyy') AS Sdate, dbo_aut_Invoice.InputUser, Count(dbo_aut_Invoice.DocCode) AS CountOfDocCode" & _
"FROM dbo_aut_Invoice" & _
"WHERE (((Format([dbo_aut_Invoice].[InputDate],'dd/mm/yyyy'=30/1/2007))<>False) AND ((dbo_aut_Invoice.CmpCode)='100'))" & _
"GROUP BY dbo_aut_Invoice.InputUser, Format([inputdate],'dd/mm/yyyy');"

DoCmd.RunSQL sql1

DoCmd.SetWarnings True
DoCmd.Close acForm, "Processing"
DoCmd.Close acForm, "Date"
'DoCmd.Hourglass False
MsgBox "Update now complete", vbOKOnly, "Input Number Update"

End Sub

Any ideas or thoughts are greatly appreciated.

Cheers,
Iain

 

[allan57]

Try the following

sql1 = "INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput ) " & _
"SELECT Format([dbo_aut_Invoice.Inputdate],'dd/mm/yyyy') AS Sdate, dbo_aut_Invoice.InputUser, Count(dbo_aut_Invoice.DocCode) AS CountOfDocCode " & _
"FROM dbo_aut_Invoice " & _
"WHERE (((Format([dbo_aut_Invoice].[InputDate],'dd/mm/yyyy'=30/1/2007))<>False) AND ((dbo_aut_Invoice.CmpCode)='100')) " & _
"GROUP BY dbo_aut_Invoice.InputUser, Format([inputdate],'dd/mm/yyyy');"

I think the problem might be missing spaces at the line ie

"INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput )" & _

needs to be

"INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput ) " & _

 

[fedupwithaccess]

SQL Statement error

Try the following

I think the problem might be missing spaces at the line ie

"INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput )" & _

needs to be

"INSERT INTO [QAInputNumbers] ( [Date], userid, ActualInput ) " & _

Thanks Allan, all works fine now.

I'll take more care next time.

Many thanks,
Iain