Run time error on sql execution 2

[Nitibob]

Hi all,

I think I'm making a schoolboy error here but I can't see it.
It is giving me a 'Run-time error: 3061' - Too Few parameters. Expecting 1'

Can someone give me any advice please?



Private Sub cmdMonth_Click()

Dim wks As Workspace
Dim dbs As Database
Dim strMonthSQL As String

Set wks = Workspaces(0)
Set dbs = CurrentDb

strMonthSQL = "SELECT tblSettledAudit.*, * INTO tblSettledReporter "
strMonthSQL = strMonthSQL + "FROM tblSettledAudit LEFT JOIN tblAuditList "
strMonthSQL = strMonthSQL + "ON tblSettledAudit.strClaimNo = tblAuditList.strClaimNo "
strMonthSQL = strMonthSQL + "WHERE tblAuditList.dtmMonth = [Forms]![fmnuMonthSelect]![cboSelector];"


dbs.Execute strMonthSQL, DB_FAILONERROR

dbs.Close



End Sub

 

[CajunCenturion]

Check the spellings of your table names and your field names.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Nitibob]

thanks CajunCenturion for your quick response, I have checked the field and table names, these are all fine - any other ideas?

Thanks

 

[CajunCenturion]

If tblAuditList.dtmMonth is of type text, you might want to try
strMonthSQL = strMonthSQL + "WHERE tblAuditList.dtmMonth = '" & [Forms]![fmnuMonthSelect]![cboSelector] & "';"


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Nitibob]

Hi there,

the field dtmDate is of type date/time - does this have any bearing on the way the code needs to be written?

 

[BillPower]

Hi Nitibob,

Try:

Dim strSQL As String
strSQL = "SELECT tblSettledAudit.*, * INTO tblSettledReporter " & _
"FROM tblSettledAudit LEFT JOIN tblAuditList " & _
"ON tblSettledAudit.strClaimNo = tblAuditList.strClaimNo " & _
"WHERE tblAuditList.dtmMonth = [Forms]![fmnuMonthSelect]![cboSelector];"
DoCmd.RunSQL (strSQL)

Bill

 

[Nitibob]

Hi Bill,

Its still giving the run time error - its expecting parameters where there is none????

I'm confused!!

 

[BillPower]

Hi Nitibob, can you can send me a copy of your DB, removing any sensitive data first. Will post any suggestions here ASAP. This is a real mystery.

billpower@cwcom.net

Bill

 

[Nitibob]

Thanks Bill, Will do...can't actually send it til later on today!!!

Thanks

 

[Nitibob]

Hi all,

I continued to play with this and got it to work at last!!!

Its a bit bizzare...but it now works... thanks to both bill and centurion for all their help!!

strMonthSQL = "SELECT tblSettledAudit.* INTO tblSettledReporter " & _
"FROM tblSettledAudit LEFT JOIN tblAuditList " & _
"ON tblSettledAudit.strClaimNo = tblAuditList.strClaimNo " & _
"WHERE tblAuditList.dtmMonth = #" & Forms!fmnuMonthSelect!cboSelector & "#; "


dbs.Execute strMonthSQL, DB_FAILONERROR