Recordset issue

[DMT Dave]

Hi guy's stuck with this one, i can't understand why there is an error of Run time error 3061 too few parameters expected 1, can someone tell where i am going wrong ? i know there is data between the mStartDate and mEndDate

Dim MailAdd As String, mYes As String, mNo As String, fldExists As StringDim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset, rs5 As DAO.Recordset, rs6 As DAO.Recordset
Dim mStartDate As Date, mEndDate As Date
Dim mYear As Long, mMonth As Long


mStartDate = Format((DateSerial(Me.txtCriteriaYear, Me.txtCriteriaMonth, 1)), "mm/dd/yyyy") 'Returns 1st day of month
mEndDate = Format((DateSerial(Me.txtCriteriaYear, Me.txtCriteriaMonth + 1, 0)), "mm/dd/yyyy") 'returns last day of month
mYes = "Yes"
mNo = "No"
MailAdd = "meathome.com"
mYear = Format(Me.txtCriteriaYear, "yyyy")
mMonth = Format(Me.txtCriteriaMonth, "mm")


Set rs = CurrentDb.OpenRecordset("Select tblInvoices.InvoiceDate, tblInvoices.Name, tblInvoices.InvoiceNumber, tblInvoices.CostOfJob, tblInvoices.VAT, tblInvoices.TotalAmount, tblInvoices.Paid, tblInvoices.PaidDate From tblInvoices WHERE InvoiceDate Between #" & mStartDate & "# And #" & mEndDate & "# ORDER BY InvoiceDate;")

[DMT Dave]

May well have sorted it, field name incorrect!!! thanks if anyone has looked

[CJ_London]

you aught to change your 'm' date variables to string since that is what the format function returns. When the string is assigned to a date variable, the date will revert to your default system date format. If that is the US format, no problem, but anywhere else and you will get problems.

as a test in the UK, in the immediate window, enter

?Format((DateSerial(2019, 10, 1)), "mm/dd/yyyy")

which returns a string
10/01/2019

which looks ok (m-d-y)

now look at it as a date by adding datevalue to convert it to a date (which is what happens when you assign it to a date variable)
?datevalue(Format((DateSerial(2019, 10, 1)), "mm/dd/yyyy"))

which still looks OK
10/01/2019 (m-d-y)

but is it? format as a medium date
?format(datevalue(Format((DateSerial(2019, 10, 1)), "mm/dd/yyyy")),"medium date")
and you get

10-Jan-19

[ssanfu]

This is how I write my code for recordsets. (in blue)

    Dim MailAdd As String, mYes As String, mNo As String, fldExists As String
    Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset, rs5 As DAO.Recordset, rs6 As DAO.Recordset
    Dim mStartDate As Date, mEndDate As Date
    Dim mYear As Long, mMonth As Long
    Dim sSQL As String

    mStartDate = Format((DateSerial(Me.txtCriteriaYear, Me.txtCriteriaMonth, 1)), "mm/dd/yyyy")    'Returns 1st day of month
    mEndDate = Format((DateSerial(Me.txtCriteriaYear, Me.txtCriteriaMonth + 1, 0)), "mm/dd/yyyy")    'returns last day of month
    mYes = "Yes"
    mNo = "No"
    MailAdd = "meathome.com"
    mYear = Format(Me.txtCriteriaYear, "yyyy")
    mMonth = Format(Me.txtCriteriaMonth, "mm")


    sSQL = "Select tblInvoices.InvoiceDate, tblInvoices.Name, tblInvoices.InvoiceNumber, tblInvoices.CostOfJob, tblInvoices.VAT,"
    sSQL = sSQL & " tblInvoices.TotalAmount, tblInvoices.Paid, tblInvoices.PaidDate+"
    sSQL = sSQL & " From tblInvoices"
    sSQL = sSQL & " WHERE InvoiceDate Between #" & mStartDate & "# And #" & mEndDate & "#"
    sSQL = sSQL & " ORDER BY InvoiceDate;"
    Debug.Print sSQL

    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    'other code

This allows you to

1) look at the SQL in the immediate window to see if the SQL is formed properly and
2) copy the SQL in the immediate window, create a new query, paste in the copied SQL and execute the query to see if there are errors or to see if the results are as expected.

[DMT Dave]

Hi Steve, brilliant thank you and AJax, it give me a better understanding