Clng; Sum error "Type MisMatch"  | Familiar Sight | | Join Date: May 2007
Posts: 222
| |
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
| | Expert | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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 | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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 | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by FishVal 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
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by FishVal 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
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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 | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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)
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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 | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Sorry, but you still didn't answer whether you tried summing without using CLng.
| | Expert | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
are [quantity], [unit price], and [discount] in the same table?
| | Expert | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
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.
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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 | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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?
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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 | | Join Date: Jul 2007 Location: BC, Canada
Posts: 289
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by imrosie 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)
| | Familiar Sight | | Join Date: May 2007
Posts: 222
| | | re: Clng; Sum error "Type MisMatch"
Quote:
Originally Posted by mlcampeau 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
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
