var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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 )
I've even tried this:
What are the data types for Quantity, UnitPrice, and Discount?
What types are the LineTotal, Quantity, UnitPrice and Discount?
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.
Your currency field is causing the problem.
P.S. Why are you using CLng?
Sum(([Quantity] * CInt([UnitPrice]) * (1-[Discount]) * 100)/100)
P.P.S Should it perhaps even be
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.
Sum(([Quantity] * CInt([UnitPrice]) *
((1-[Discount]) * 100 ))/100)
Should I change currency format to a number field?
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
Error msg - "The expression you entered contains invalid syntax" for:
Line Total:Sum(([Quantity] * CInt([UnitPrice]) * (1-[Discount]) * 100)/100)
Line Total:Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100)/100)
The second one has 5 opening parens and 4 closing parens.
I don't want to be picky (or may I do !), but I think the 100s are also redundant
Line Total:Sum([Quantity] * CInt([UnitPrice]) * (1-[Discount]))
I am also a little puzzled why currency need converting to an integer ??
Because it's a different datatype..?
The 100s are irrelevant unless
Originally Posted by
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
are also redundant.
Sum(([Quantity] * CInt([UnitPrice]) * ((1-[Discount]) * 100))/100)
(a * b * c) / 100 <> (a * b * (c * 100)) / 100
abc/100 <> 100abc / 100
EDIT: Scratch that, I mis-read what you were saying
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?
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!
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
However, this does not solve the probelm, would it be possible to post the complete query?
Sum([Quantity] * [UnitPrice] * (1-[Discount] / 100))