Results 1 to 5 of 5

Recordset issue

  1. #1
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    198

    Recordset issue

    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



    Code:
    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;")

  2. #2
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    198
    May well have sorted it, field name incorrect!!! thanks if anyone has looked

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    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

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    This is how I write my code for recordsets. (in blue)
    Code:
        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.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    198
    Hi Steve, brilliant thank you and AJax, it give me a better understanding

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Recordset to update date/time Issue
    By excellenthelp in forum Access
    Replies: 7
    Last Post: 12-19-2014, 12:51 PM
  2. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  3. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  4. Navigation in DAO Recordset Issue
    By d9pierce1 in forum Programming
    Replies: 21
    Last Post: 01-12-2012, 10:27 AM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums