Compare currency to a double

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am reading data from two different spreadsheets. One is a type currency
and the other is a double. When the two values are different, it suppose the
perform the process but it doesn't do it. What do I need to do?
 
I am reading data from two different spreadsheets. One is a type currency
and the other is a double. When the two values are different, it suppose the
perform the process but it doesn't do it. What do I need to do?
Click to expand...

I'd suggest using the CCur() function - and possibly the Round()
function as well - to convert the Double value to Currency. I suspect
you're getting bit by roundoff error; Double values are accurate to
some 14 decimal places, but some numbers ( 0.1 is an example) cannot
be represented exactly and will be different fourteen places out. Thus
you might have a Currency value of 1287.55 being matched to a Double
value of 1287.54999999998... and it will fail.

If your currency values are all two decimal places precision, match
them to

CCur(Round([doublefield], 2))

John W. Vinson[MVP]
 
That did work, but I have another question that deals with the same concept.
I have an if statement that checks whether the values are not equal. At one
point I have a value in one and null in the other, but it did not perform the
procedure. I can't use isnull because sometimes there are values that are
not equal and I need that value to update the spreadsheet. What am I doing
incorrectly?

John Vinson said:
I am reading data from two different spreadsheets. One is a type currency
and the other is a double. When the two values are different, it suppose the
perform the process but it doesn't do it. What do I need to do?
Click to expand...

I'd suggest using the CCur() function - and possibly the Round()
function as well - to convert the Double value to Currency. I suspect
you're getting bit by roundoff error; Double values are accurate to
some 14 decimal places, but some numbers ( 0.1 is an example) cannot
be represented exactly and will be different fourteen places out. Thus
you might have a Currency value of 1287.55 being matched to a Double
value of 1287.54999999998... and it will fail.

If your currency values are all two decimal places precision, match
them to

CCur(Round([doublefield], 2))

John W. Vinson[MVP]
Click to expand...
 
That did work, but I have another question that deals with the same concept.
I have an if statement that checks whether the values are not equal. At one
point I have a value in one and null in the other, but it did not perform the
procedure. I can't use isnull because sometimes there are values that are
not equal and I need that value to update the spreadsheet. What am I doing
incorrectly?
Click to expand...

I don't know, because you did not post your code, so I don't know what
you're doing.

One thing to consider is that NULL is not equal to anything, even to
NULL; nor is it unequal to anything. Any comparison to NULL returns...
NULL.

You can sometimes use NZ([fieldname], -1)

to replace NULL by some value which will never be used (-1 in my
example, you may need some other number); or a syntax

[FieldA] <> [FieldB] OR [FieldB] IS NULL


John W. Vinson[MVP]
 
That worked also. You are definitely the MVP!!!!!

John Vinson said:
That did work, but I have another question that deals with the same concept.
I have an if statement that checks whether the values are not equal. At one
point I have a value in one and null in the other, but it did not perform the
procedure. I can't use isnull because sometimes there are values that are
not equal and I need that value to update the spreadsheet. What am I doing
incorrectly?
Click to expand...

I don't know, because you did not post your code, so I don't know what
you're doing.

One thing to consider is that NULL is not equal to anything, even to
NULL; nor is it unequal to anything. Any comparison to NULL returns...
NULL.

You can sometimes use NZ([fieldname], -1)

to replace NULL by some value which will never be used (-1 in my
example, you may need some other number); or a syntax

[FieldA] <> [FieldB] OR [FieldB] IS NULL


John W. Vinson[MVP]
Click to expand...
 
Back
Top