Update type conversion error with nulls

[DonnaJN]

I need to simply divide one currency value by another currency value. Sounds
simple????? Here are two sql statements I've tried. Most records are null,
it is only the ones that I want to update and close that will have a value in
Net_PL.

UPDATE Diagonal SET Diagonal.Final_ROR = IIf([Diagonal]![Net_PL] Is
Null,0,[Diagonal]![Net_PL]/[Diagonal]![Tr_Trade_Risk]);

UPDATE Diagonal SET Diagonal.Final_ROR =
Nz([Diagonal]![Net_PL])/Nz([Diagonal]![Tr_Trade_Risk]);

I get the error message "...didn't update x number of fields due to a type
conversion error......."

I don't know what else to try.

 

[bhicks11 via AccessMonster.com]

Is the field you are updating numeric?

Bonnie
http://www.dataplus-svc.com

I need to simply divide one currency value by another currency value. Sounds
simple????? Here are two sql statements I've tried. Most records are null,
it is only the ones that I want to update and close that will have a value in
Net_PL.

UPDATE Diagonal SET Diagonal.Final_ROR = IIf([Diagonal]![Net_PL] Is
Null,0,[Diagonal]![Net_PL]/[Diagonal]![Tr_Trade_Risk]);

UPDATE Diagonal SET Diagonal.Final_ROR =
Nz([Diagonal]![Net_PL])/Nz([Diagonal]![Tr_Trade_Risk]);

I get the error message "...didn't update x number of fields due to a type
conversion error......."

I don't know what else to try.

 

[Jerry Whittle]

If you only want to update when the [Net_PL] has data, put Is Not Null in the
criteria to exclude the null records.

If there are any null or records with 0 in Tr_Trade_Risk, that could also
cause problems especially a divide by 0 error.

Another possibility, if it meets with your business rules, is to an update
query that changed the nulls in [Net_PL] to 0.

 

[KARL DEWEY]

Try this --
UPDATE Diagonal SET [Diagonal].[Final_ROR] = IIF([Diagonal].[Net_PL] Is Null
OR [Diagonal].[Net_PL] = 0 OR [Diagonal].[Tr_Trade_Risk] Is Null OR
[Diagonal].[Tr_Trade_Risk] = 0, [Diagonal].[Final_ROR],
[Diagonal].[Net_PL]/[Diagonal].[Tr_Trade_Risk]);