Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    record count (97 sr2)

    I keep getting 3061 too few parameters expected 1 when the set rst2 line excutes. I have no idea why. Any help would be appreciated. I am trying to make sure a duplicate record (date/rep) does not get into the table.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: record count (97 sr2)

    Dates should be enclosed in # characters instead of quotes. And there should be a space before the word "where". Try

    strsql = "SELECT Count(*) AS RecCount FROM [monthly sales dollars] WHERE [rep] = " & Chr(34) & repnumber & Chr(34) & "AND [daterec] = #" & recdate & "#"

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record count (97 sr2)

    I am still getting the same error

    Private Sub CMDADD_Click()
    Dim rst As Recordset, DB As Database, recct, strsql As String, recdate As Date
    Dim repnumber As String, rst2 As Recordset

    repnumber = Me![txtrep]
    recdate = Me![TXTDATE]

    Set DB = CurrentDb
    strsql = "SELECT Count(*) AS RecCount FROM [monthly sales dollars] WHERE [rep] = " & Chr(34) & repnumber & Chr(34) & "AND [daterec] = #" & recdate & "#"
    Set rst2 = DB.OpenRecordset(strsql, dbOpenDynaset)
    recct = rst2.recordcount

    [daterec] is a date in the monthly sales dollars table does that have to have # around it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: record count (97 sr2)

    No, the field name doesn't need # around it. The error message means that one of the names you use is not recognized.
    Make sure that monthly sales dollars is the name of the table, and that rep and daterec are fields in this table, spelled correctly.

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record count (97 sr2)

    thanks that was the problem...

    You are the best!!!

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: record count (97 sr2)

    But does it actually give the right answer??

    It looks to me ( and maybe I am making an embarrassing mistake) that recct will always turn out to be 1.

    If the query itself does the counting, then the recordset will only have one record. In that case you could find the count using a dlookup.
    If you want to count the records in a recordset, use a query that returns all the records you are interested in.

    Also rst2.recordcount does not return an accurate figure unless you precede it with rst2.MoveLast.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record count (97 sr2)

    It did give me the results you said (always1) but I reset the redct =0 right before I set the DB and it seems to work. I will have to keep a careful eye on this.
    Again thanks for all you help
    Cathy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •