Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: help! error "too few parameters" and i cant solve it.

    hey all, i would really appreciate any help!

    this is what im trying to do:
    i have a DB which holds details about companies i work with and appeals ive made to those companies.

    in VB i wrote the following synt:

    Dim rst As Recordset
    Dim strSQL As String
    Dim intNum As Integer

    intNum = Me.CompanyID

    strSQL = "SELECT Count(tbl_Appeal.AppealID) AS [Count] FROM
    tbl_Company LEFT JOIN tbl_Appeal ON tbl_Company.CompanyID
    = tbl_Appeal.AppealCompany WHERE tbl_Company.CompanyID =
    intNum GROUP BY tbl_Company.CompanyID;"


    Set rst = CurrentDb.OpenRecordset(strSQL)

    it tells me "too few parameters, Expected 1".
    i checked the SQL statement in the Query Object and it worked! the query is good, and yet it wont work in my VB module.

    anyone?


    thanks ,
    Zohar.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one thing to always do is check the SQL reads as you expect, so do a msgbox strSQL before assigning the value to the open recordset function, that way round you can see if what you are sending to the function is what you expect, or if its valid. Its very easy to see code on a screen and think that what is in your minds eye is what you are sending to the computer.

    first guess would be that the [count] is the problem... you probably should not use a SQL reserved word.. so I'd call it something else eg
    Code:
    Count(tbl_Appeal.AppealID) AS NoOfAppeals
    mind you Im not to sure what intnum comes from.. is that a column in a table or a value you are trying to supply as a parameter.. in which case you need to drop out of the quote marks
    Code:
    tbl_Company.CompanyID = " & intNum  & " GROUP By
    if its a string / text value you need to enclose it in quotes
    Code:
    tbl_Company.CompanyID = '" & intNum  & "' GROUP By
    OR
    Code:
    tbl_Company.CompanyID = " & chr$(34) & intNum & chr$(34)  & " GROUP By
    some people find it easier to read the SQL with chr$(34) as it doesn't send the editor into a tizz.


    try the sql in the sql browser, replacing your parameters with known good values

    open the query designer
    select the the SQL view (left most button)
    paste you SQL
    when you know your SQL works paste it back into the program or save it as a query

  3. #3
    Join Date
    Mar 2008
    Posts
    2
    Thank you so much!
    u have no idea how happy i am right now.

    i did all 3 of the things u said:
    1. i put a msgbox. i immideatly noticed the intNum was written "intNum" and
    not the number value it repressents.

    2. i changed from the word "count" to another, not unique to the program.

    3. ive added the "& intNum &".

    it worked!!
    if u can just explain to me one thing so i wont do that rocky mistake again,
    can u explain to me why i needed to put the "& &" ? because i built the query and then i copied it to the VB. the SQL view of the query isnt good enough?

    again thank u so much ! :-)

  4. #4
    Join Date
    Mar 2008
    Posts
    2
    3. ive added the "& intNum &".--------that is wrong.Because intNum is not a sql field ,it is a vb variable. u are building a string.
    try to do this:

    dim intNum as long
    intNum = 123

    strSQL = "SELECT Count(tbl_Appeal.AppealID) AS [Count] FROM
    tbl_Company LEFT JOIN tbl_Appeal ON tbl_Company.CompanyID
    = tbl_Appeal.AppealCompany WHERE tbl_Company.CompanyID = " &
    intNum & " GROUP BY tbl_Company.CompanyID;"
    debug.print strSQL

    'it print out : SELECT Count(tbl_Appeal.AppealID) AS [Count] FROM
    tbl_Company LEFT JOIN tbl_Appeal ON tbl_Company.CompanyID
    = tbl_Appeal.AppealCompany WHERE tbl_Company.CompanyID = 123 GROUP BY tbl_Company.CompanyID;
    'it is you want.

Posting Permissions

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