467,136 Members | 1,275 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,136 developers. It's quick & easy.

Clng; Sum error "Type MisMatch"

imrosie
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



Expand|Select|Wrap|Line Numbers
  1. LineTotal: Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)
Rosie
Aug 2 '07 #1
  • viewed: 4518
Share:
17 Replies
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



Expand|Select|Wrap|Line Numbers
  1. 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))
Aug 2 '07 #2
FishVal
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



Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '07 #3
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
Aug 2 '07 #4
imrosie
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
Aug 2 '07 #5
imrosie
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
Aug 2 '07 #6
imrosie
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):
Expand|Select|Wrap|Line Numbers
  1. CLng(Sum(([Quantity]*[UnitPrice]*(1-[Discount])*100)/100))
thanks anyway
Rosie
Aug 2 '07 #7
imrosie
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):
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '07 #8
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?
Expand|Select|Wrap|Line Numbers
  1. Sum(([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)
Aug 2 '07 #9
imrosie
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?
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '07 #10
Expert 100+
Sorry, but you still didn't answer whether you tried summing without using CLng.
Aug 2 '07 #11
Expert 100+
are [quantity], [unit price], and [discount] in the same table?
Aug 2 '07 #12
Expert 100+
I just made a sample table with fields Quanity: Number, UnitPrice: Currency, Discount:Number and tried
Expand|Select|Wrap|Line Numbers
  1. Sum(([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)
and that worked for me. So did
Expand|Select|Wrap|Line Numbers
  1. Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)
so I'm not too sure why it won't work for you.
Aug 2 '07 #13
imrosie
100+
I just made a sample table with fields Quanity: Number, UnitPrice: Currency, Discount:Number and tried
Expand|Select|Wrap|Line Numbers
  1. Sum(([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)
and that worked for me. So did
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '07 #14
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?
Aug 2 '07 #15
imrosie
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
Aug 2 '07 #16
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)
Aug 2 '07 #17
imrosie
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
Aug 2 '07 #18

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
5 posts views Thread by Davros9@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.