Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: Clng; Sum error "Type MisMatch"

    I've tried this string to Sum up payments, moving brackets and parenthesis different ways, I still get error 'type mismatch'....not sure why. Can anyone point me see the error??

    Here's the code: ( text )
    LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)

    I've even tried this:
    LineTotal: Sum([Quantity]*[UnitPrice]*(1-[Discount])*100)/100

    imrosie

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    What are the data types for Quantity, UnitPrice, and Discount?

  3. #3
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    What types are the LineTotal, Quantity, UnitPrice and Discount?

  4. #4
    Join Date
    Aug 2007
    Posts
    102
    Hi Blade1981m,

    Line Total is an alias for the 'sum' expression
    UnitPrice = Currency
    Quantity = Number
    Discount = Number
    The 3 fields are in the same table "OrderDetails"... I built a query with this table and other tables. The expression is in the query.
    thanks

    imrosie

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha!
    Your currency field is causing the problem.
    Try
    Code:
    Sum(([Quantity] * CInt([UnitPrice]) * (1-[Discount]) * 100)/100)
    P.S. Why are you using CLng?
    P.P.S Should it perhaps even be
    Code:
    Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100))/100)
    I assume that the bit in red is supposed to represent a percentage (E.g. 0.10)? Otherwise your * 100 and / 100 cancel each other out.
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Posts
    102

    Should I change currency format to a number field?

    Hello GeorgeV,

    Yes, the bit in red is a discount. thanks for your help

    I tried both suggestions; I received different responses in each case(single or double parentheis in front of 1-Discount:

    Error msg -"The expression you entered has an invalid . or !operator or invalid parenthesis

    Code:
    Line Total:Sum(([Quantity] * CInt([UnitPrice]) * (1-[Discount]) * 100)/100)
    Error msg - "The expression you entered contains invalid syntax" for:
    Code:
    Line Total:Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100)/100)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The second one has 5 opening parens and 4 closing parens.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I don't want to be picky (or may I do !), but I think the 100s are also redundant
    Code:
    Line Total:Sum([Quantity] * CInt([UnitPrice]) * (1-[Discount]))
    ??

    I am also a little puzzled why currency need converting to an integer ??

    MTB

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because it's a different datatype..?

    The 100s are irrelevant unless
    Quote Originally Posted by georgev
    Code:
    Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100))/100)
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I have never had to convert currency before, and if the UnitPrice is 10.99 say you are loosing 9% of the of the value ??

    I think the 100s in this
    Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100))/100)
    are also redundant.

    MTB

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    (a * b * c) / 100 <> (a * b * (c * 100)) / 100

    simplified
    abc/100 <> 100abc / 100


    EDIT: Scratch that, I mis-read what you were saying
    George
    Home | Blog

  12. #12
    Join Date
    Aug 2007
    Posts
    102
    Hi MiketheBike and GeorgeV,

    I've read your comments. I have just tried both of your expressions, neither works. I still get type mismatch, that's after I changed my currency variables to numbers, with and without the 100's(redundant).

    Now what should I do?

    thanks

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How can we get a type mismatch on
    number * number * number ???

    My only other thought would be that the sum may be causing you some problems. Drop that from your equation and see if the error still occurs!
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    My only other thought on this is that Discount cannot be a number (this is usually a positive percentage as a decimal? ie Single type).

    If it is a number (ie integer), then 1-[Discount] will be zero or negative, in which case you will need a 100 back in, but with different bracket positions. ie
    Code:
    Sum([Quantity] * [UnitPrice] * (1-[Discount] / 100))
    However, this does not solve the probelm, would it be possible to post the complete query?

    MTB

Posting Permissions

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