Trouble comparing number values in formula field
Alright, so I have two tables, say Order Sheets and Order Items
Each Order Sheet can have multiple Order items.
Each Order Sheet has a field labeled Order Amount
Each Order Item has a Quantity field(numeric) and a Price(currency) field.
I have created a formula field Line Total that totals each Order Items record by doing [Quantity] * [Price]
I also have a summary field on Order Sheets called Total Items that sums all the Line Totals
I then created a formula field to check if the Order Amount is equal to Total Items and if not displays a value let's say
If ([Order Amount] <> [Total Items], "Not Equal")
No matter what I do, I cannot get the values to check as equal.
I know the values are the same because the Order Amount is user input as 29.90, which QB stores as 29.9. I also know the two items are 5x$2.99 and the sum of those two is 29.9
I have tried every variation of options in QB to display each field as a number, currency, decimal points, etc.
I have tried using conversions such as ToNumber on the values as well.
I have viewed the number values on each and know they both are 29.9
I have tried rounding function on values.
I am stumped here. I even tried setting all currency fields to numeric assuming it was a type mismatch issue.
But for whatever reason, QB still fails to say they are equal.
Any ideas?
Each Order Sheet can have multiple Order items.
Each Order Sheet has a field labeled Order Amount
Each Order Item has a Quantity field(numeric) and a Price(currency) field.
I have created a formula field Line Total that totals each Order Items record by doing [Quantity] * [Price]
I also have a summary field on Order Sheets called Total Items that sums all the Line Totals
I then created a formula field to check if the Order Amount is equal to Total Items and if not displays a value let's say
If ([Order Amount] <> [Total Items], "Not Equal")
No matter what I do, I cannot get the values to check as equal.
I know the values are the same because the Order Amount is user input as 29.90, which QB stores as 29.9. I also know the two items are 5x$2.99 and the sum of those two is 29.9
I have tried every variation of options in QB to display each field as a number, currency, decimal points, etc.
I have tried using conversions such as ToNumber on the values as well.
I have viewed the number values on each and know they both are 29.9
I have tried rounding function on values.
I am stumped here. I even tried setting all currency fields to numeric assuming it was a type mismatch issue.
But for whatever reason, QB still fails to say they are equal.
Any ideas?
