100+ 
I've tried this string to Sum up payments, moving brackets and paren's different ways, I still get error 'type mismatch'....not sure why. Can anyone point me in the right direction?? thanks  LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
Rosie
 
Share:
Expert 100+ 
I've tried this string to Sum up payments, moving brackets and paren's different ways, I still get error 'type mismatch'....not sure why. Can anyone point me in the right direction?? thanks  LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
Rosie
My guess would be that it has to do with the CLng. Have you tried doing the Sum without it?
Maybe try it this way:
CLng(Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100))
  Expert 2GB 
I've tried this string to Sum up payments, moving brackets and paren's different ways, I still get error 'type mismatch'....not sure why. Can anyone point me in the right direction?? thanks  LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
Rosie
What is a reason for using CLng function.
BTW [Quantity]*[UnitPrice]*(1[Discount]) will be negative when [Discount]>1
  Expert 100+ 
BTW [Quantity]*[UnitPrice]*(1[Discount]) will be negative when [Discount]>1
My guess is that [Discount] is a percentage in decimal form so it probably won't ever be greater than 1. But I can't speak for imrosie, so I could be wrong
  100+ 
My guess would be that it has to do with the CLng. Have you tried doing the Sum without it?
Maybe try it this way:
CLng(Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100))
Mlcampeau,
No I will give that a try though,,,thanks
Rosie
  100+ 
What is a reason for using CLng function.
BTW [Quantity]*[UnitPrice]*(1[Discount]) will be negative when [Discount]>1
FishVal,
No particular reason for it,,,I saw it in a book and it looked like could work for what I was trying to do. the [Discount] is for 'less than' 1 not greater than. you're right in that case, it would be negative.
Rosie
  100+ 
My guess is that [Discount] is a percentage in decimal form so it probably won't ever be greater than 1. But I can't speak for imrosie, so I could be wrong
MLcampeau,
You're so right, the [Discount] is a percentage in decimal form, it will never be greater than 1. thanks for your input. However, I just tried your suggestion, but it didn't work...
(Maybe try it this way):  CLng(Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100))
thanks anyway
Rosie
  100+ 
MLcampeau,
You're so right, the [Discount] is a percentage in decimal form, it will never be greater than 1. thanks for your input. However, I just tried your suggestion, but it didn't work...
(Maybe try it this way):  CLng(Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100))
there's no need for the extra set of paren's ....thank but it's the 'clng' it's really complaining about.
thanks anyway
Rosie
there's no need for the extra set of paren's ....thank but it's the 'clng' it's really complaining about.
thanks anyway
  Expert 100+ 
there's no need for the extra set of paren's ....thank but it's the 'clng' it's really complaining about.
thanks anyway
oops, didn't mean to put extra paren's in there. I was just suggesting trying to use the CLng outside of the Sum function. Have you tried summing without CLng?  Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
  100+ 
oops, didn't mean to put extra paren's in there. I was just suggesting trying to use the CLng outside of the Sum function. Have you tried summing without CLng?  Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
Hi MLcampeau,,,,
Even with the extra paren's removed, it doesn't work, it's still complains about a type mixmatch. I think that I've got to look at the tables and figure out why summing
quantity, unit price and discount is a problem
UnitPrice = Currency
Quantity = Number
Discount = Number
Could this be the problem? thanks
Rosie
  Expert 100+ 
Sorry, but you still didn't answer whether you tried summing without using CLng.
  Expert 100+ 
are [quantity], [unit price], and [discount] in the same table?
  Expert 100+ 
I just made a sample table with fields Quanity: Number, UnitPrice: Currency, Discount:Number and tried  Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
and that worked for me. So did  Sum(CLng([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
so I'm not too sure why it won't work for you.
  100+ 
I just made a sample table with fields Quanity: Number, UnitPrice: Currency, Discount:Number and tried  Sum(([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
and that worked for me. So did  Sum(CLng([Quantity]*[UnitPrice]*(1[Discount])*100)/100)
so I'm not too sure why it won't work for you.
Wow that's amazing....its in a query, one of the columns, so would that make a difference?
yes, I tried summing without the 'clng' and yes, all those variables are in the same table. That table (order details) is one of the tables in the query which has this 'sum' expression.
Rosie
  Expert 100+ 
Wow that's amazing....its in a query, one of the columns, so would that make a difference?
Rosie
Are you saying that you're putting the expression in the query? Because rereading my post I wasn't very clear. I made a table with those three fields, then I created a query with the expression. Are your three fields in one table?
  100+ 
Are you saying that you're putting the expression in the query? Because rereading my post I wasn't very clear. I made a table with those three fields, then I created a query with the expression. Are your three fields in one table?
Yes, the three fields are in the same table (that table is "OrderDetails" which is a part of the 'Receivables Aging query. There are 3 tables along with query called "Sum of Payments, in this whole Receivables Aging query.
thanks
Rosie
  Expert 100+ 
Yes, the three fields are in the same table (that table is "OrderDetails" which is a part of the 'Receivables Aging query. There are 3 tables along with query called "Sum of Payments, in this whole Receivables Aging query.
thanks
Rosie
Try specifying which table the fields belong to:
LineTotal: Sum(CLng([OrderDetails].[Quantity]*[OrderDetails].[UnitPrice]*(1[OrderDetails].[Discount])*100)/100)
  100+ 
Try specifying which table the fields belong to:
LineTotal: Sum(CLng([OrderDetails].[Quantity]*[OrderDetails].[UnitPrice]*(1[OrderDetails].[Discount])*100)/100)
MLCampeau,
I appreciate your sticking with me on this....I'm going off to try that now...will get back with the results..
Rosie
  Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
1 post
views
Thread by dmgauntt2002@yahoo.com 
last post: by

8 posts
views
Thread by eminemence@gmail.com 
last post: by

4 posts
views
Thread by bob95226@yahoo.com 
last post: by

1 post
views
Thread by ApexData@gmail.com 
last post: by

5 posts
views
Thread by Davros9@gmail.com 
last post: by
              