Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    22

    Unanswered: Too Few Parameters "Error Msg 3061"

    Hey All,

    So I am writing this code to essentially count and display number of records to the user on screen in a msgbox. I am getting this error msg saying that

    "Run-Time Error '3061':
    Too few parameters. Expected 6"

    Here is the code
    Code:
                Region = Region2.Value
                dmf1 = DMF.Value
                dmt1 = DMT.Value
                dyf1 = DYF.Value
                dyt1 = DYT.Value
                whr = "((MasterPipe_1.DueMonth) Between dmf1 And dmt1) AND ((MasterPipe_1.DueYear) Between dyf1 And dyt1)"
                strSQL = "SELECT Count(MasterPipe.Priority) AS [Count of Record] FROM MasterPipe WHERE (((MasterPipe.Region) = Region) AND " & whr & " AND (MasterPipe.Priority)=4) GROUP BY MasterPipe.Priority;"
                MsgBox strSQL
                Set cn = CurrentDb
                Set rs = cn.OpenRecordset(strSQL, dbOpenDynaset)
    Please help, I need to repeat the same query a few times to capture all possibilities.

    Thank you!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you don't say which line throws the error
    this sor tof meesage usually means the VBA compiler has got its knickers in a twist becuase of either missing or misplaced punctuation ro you as a developer haven't supplied a parameter that is required as part of the sub/function

    what does your SQL look like (as opposed to what you have displayed which is the VBA creatingthre SQL

    it could be that you are missing a ' or " as these are needed to delimit sring literals
    it could be because your dates may need expressing as sate literals (in us format #mm/dd/yyyy#)

    to display the SQL set a watch/breakpoint int he code at then examine variabels as required when the code halts
    or take a screen shot of the msgbox output
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    22
    Hey Healdem,

    The error msg comes a when I am opening the record set. Secondly i made a few more changes so i could break this code up rather than the whole things a string here is how it looks now:

    Region = Region2.Value
    dmf1 = DMF.Value
    dmt1 = DMT.Value
    dyf1 = DYF.Value
    dyt1 = DYT.Value

    sel = "SELECT Count(MasterPipe.Priority) AS [Count of Record]"
    frm = " FROM MasterPipe"
    whr = " WHERE (((MasterPipe.Region) = Region) AND ((MasterPipe.DueMonth) Between dmf1 And dmt1) AND ((MasterPipe.DueYear) Between dyf1 And dyt1) AND (MasterPipe.Priority)=4)"
    grp = " GROUP BY MasterPipe.Priority;"

    strSQL = sel & frm & whr & grp
    'strSQL = "SELECT Count(MasterPipe.Priority) AS [Count of Record] FROM MasterPipe WHERE (((MasterPipe.Region) = Region) AND ((MasterPipe.DueMonth) Between dmf1 And dmt1) AND ((MasterPipe.DueYear) Between dyf1 And dyt1) AND (MasterPipe.Priority)=4) GROUP BY MasterPipe.Priority;"
    MsgBox strSQL
    Set cn = CurrentDb
    Set rs = cn.OpenRecordset(strSQL, dbOpenDynaset) ' Error At this statement
    If Not rs.EOF Then
    MsgBox ("Number of (" & RevLevels.Value & ") records for the region: " & Region & " are: " & rs.Fields(0)), vbExclamation
    Else
    MsgBox "no records returned (or error?)"
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Additionally, I have already the dates broken out by month and year in my data. I am asking the user for a month range and then the year range which i would like to include in my query. If i dont have the between statements for Month and Year, the code runs perfectly! so i think it something got to do with the new statements which are added now...

    Let me know if you see anything out of place here

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Your problem is possibly that you are building a string out of variables, but using the variables as literals.
    Code:
    whr = " WHERE (((MasterPipe.Region) = Region) AND ((MasterPipe.DueMonth) Between dmf1 And dmt1) AND ((MasterPipe.DueYear) Between dyf1 And dyt1) AND (MasterPipe.Priority)=4)"
    This is not valid, as Access will not be able to interpret the literal strings "dmf1" and "dmt1" as dates. Instead, try:
    Code:
    whr = " WHERE (((MasterPipe.Region) = Region) AND ((MasterPipe.DueMonth) Between #" & dmf1  & "# And #" & dmt1 & "#) AND ((MasterPipe.DueYear) Between #" & dyf1 & "# And "# dyt1 & "#) AND (MasterPipe.Priority)=4)"
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2013
    Posts
    22
    Thank you guys! it worked! You guys are the best!!

    Thanks again!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    glad its all cleared up
    just a comment
    you would probably have spotted this on your own had you looked at the actual SQL rather than the VB code that created the SQL. they are not neccesarily the same.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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