Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation in sql select statement (A2k3)

    Hi All,
    I'm confused (OK so what else is new) <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    I've got a list box which pulls a commission rate (as well as other info) from a table

    dblCommissionRate = CDbl(ctl.Column(4, itm))

    however, when I try and use it in a calculation in the select portion of an sql statement I get a "Too few parameters, expected 1" error.
    "SELECT DISTINCT [fld1], [fld2], (([AmountDue] - [FeeTotal]) * dblCommissionRate) AS ConCommission "

    But if I put in the actual commission rate, the correct amount is returned.
    "SELECT DISTINCT [fld1], [fld2], (([AmountDue] - [FeeTotal]) * .07) AS ConCommission "

    In all places this parameter is 'dimmed' as Double as well as me trying to ensure that it is by using CDbl in the code. When using a breakpoint it shows as 0.07.

    What am I missing?
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: Calculation in sql select statement (A2k3)

    An SQL statement is a string. If you put dblCommisionRate in it, the SQL interpreter will see if it is a field name or a known function. If it is not recognized as either of these, it is interpreted as a parameter. SQL knows nothing about VBA variables in your code. Try this:

    "SELECT DISTINCT [fld1], [fld2], (([AmountDue] - [FeeTotal]) * " & dblCommissionRate & ") AS ConCommission "

    By putting the variable outside the quotes and concatenating it with the parts of the SQL statement, the value of dblCommisionRate is used instead of the name.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation in sql select statement (A2k3)

    Hi Hans,
    Thank you. Sheesh, I feel kinda dumb (OK that's not new either). <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    I knew it would have been something simple and really, I_do_know_that an sql statement is a string, but I guess I've been too busy playing with my mental blocks to have remembered that simple fact.
    Thank you again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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