If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Update type conversion error with nulls
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 |
#3
|
|||
|
|||
Update type conversion error with nulls
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. |
#4
|
|||
|
|||
Update type conversion error with nulls
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. |
Thread Tools | |
Display Modes | |
|
|