I have a database with 3-digit fields. I have the fields as numeric and/or a string to be able to manipulate them on either way. I wan to convert each digit field to its close 10-multiple. Example
100==>No change
101==>Change to 100
349==>Change to 350
223==>Change to 220
190==>No Change
015==>Change to 020
013==>Change to 010
276==>Change to 280
007==>Change to 010
002==>Change to 000
My "rule' is that numbers over 5 to change them to next ten value. Numbers below 4 to the nearest 10 value.
The query below, does the job. However, when the field has values below 100 and the middle number is >=5, the fields looses the format. Instead of showing "060" it shows "60". For some reason it looses its format. I tried to use the AND/OR but I guess it doesn't exist on Access build event.
Code:LWry: IIf(Right([KPL_Wrist_y],1)="0",[KPL_Wrist_y],IIf(Right([KPL_Wrist_y],1)>="5",CStr(CInt(Left([KPL_Wrist_y],2))+1)+"0",IIf(Right([KPL_Wrist_y],1)<="3",Left([KPL_Wrist_y],2)+"0",Left([KPL_Wrist_y],2)+"0")))
Can anyone take a look and please give me a hint?
Thanks