Recordset error Too few parameters. Expected 2. (Error 3061)

[starclassic]

Hello eveyone,
I created a query below and supply the date from form frmQueue but when clicked on firstContact button gives me a error.
debug would show on
Set rst = dbs.OpenRecordset("qryFirst")
Any help greatly appreciated.

~~~~~~~~~~~~~~~~~~qryFirst~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT tblCollectionsData.PremCancelDate, tblCollectionsData.CancelDate, tblCollectionsData.Class, tblCollectionsData.PolicyType, tblCollectionsData.ContactMain
FROM tblCollectionsData
WHERE (((tblCollectionsData.PremCancelDate) Is Not Null) And ((tblCollectionsData.CancelDate) Between Forms!frmQueue!txtStartCanDate And Forms!frmQueue!txtEndCanDate) And ((tblCollectionsData.ContactMain)="0"));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFirstContact_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim LetterCount As Integer
Dim LetterMessage As String
Dim stDocName As String
Dim StrSql As String


Set dbs = CurrentDb
LetterCount = DCount("*", "qryFirst")
* Set rst = dbs.OpenRecordset("qryFirst") *
DoCmd.SetWarnings False

If LetterCount = 0 Then
MsgBox ("You have " & LetterCount & " First Letter to Send."), vbInformation, "Collections Database"
Exit Sub
Else
MsgBox ("You have " & LetterCount & " First Letter to Send."), vbInformation, "Collections Database"

 

[genomon]

Try putting the # character around the date fields in the Between clause:
Between #Forms!frmQueue!txtStartCanDate# And #Forms!frmQueue!txtEndCanDate#

Silence is golden.
Duct tape is silver.

 

[Remou]

Is Forms!frmQueue still open when you run this code?

 

[starclassic]

Yes the form frmQueQue is open as button Private Sub cmdFirstContact_Click() and the txtStartCanDate and txtEndCanDate are on the frmQueue.

 

[Remou]

I was not thinking. You will not be able to use a query that references a form as a recordset, however, this is easy to fix. Use the sql string and replace the references to the form with actual values:

[tt]strSQL="SELECT tblCollectionsData.PremCancelDate, " _
& "tblCollectionsData.CancelDate, " _
& "tblCollectionsData.Class, " _
& "tblCollectionsData.PolicyType, " _
& "tblCollectionsData.ContactMain " _
& "FROM tblCollectionsData " _
& "WHERE tblCollectionsData.PremCancelDate Is Not Null " _
& "And tblCollectionsData.CancelDate Between #" & Format(Forms!frmQueue!txtStartCanDate,"yyyy/mm/dd") _
& "# And #" & Format(Forms!frmQueue!txtEndCanDate ,"yyyy/mm/dd") _
& "# And tblCollectionsData.ContactMain='0'"

Set rst = dbs.OpenRecordset(strSQL)[/tt]

A text field containing zero (0) is a little odd.

 

[starclassic]

Thanks Remou,

Its working fine now.