Update type conversion error with nulls

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.

Is the field you are updating numeric?

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

DonnaJN wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DonnaJN" wrote:

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.

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]);


--
Build a little, test a little.


"DonnaJN" wrote:

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.