Populate two new fields by splitting data from existing field

[betheball]

I have a field in my db that is numeric with a size of "Decimal" and a scale of 1. Most values in the field are like 19.3, 27.3, etc. However, others are whole numbers such as 25, 37, etc. I am trying to write a query that will populate two new fields, one with the number left of the decimal and the other with the number right of the decimal. The query below works except for the fields where the value has nothing to the right of the decimal.

UPDATE SplitNumberTest SET myNumberLeft = Left(Format(FormatNumber(myNumber,1),Instr(myNumber,".")), myNumberRight=Right(FormatNumber(myNumber,1),Len(myNumber)-Instr(myNumber,"."));

When I run the query, I am alerted that one field did not update due to a type conversion failure. Any thoughts on how I can accomplish this task?

 

[Brianwarnock]

Hi
It struck me that you needed to test whether you had a decimal point or not but when I tried testing for the Instr returning 0 I couldn't get it to work so I tried a different approach and it seems ok

UPDATE table2 SET table2.mynumberleft = Int(mynumber), table2.mynumberright = IIf([mynumber]-[mynumberleft]=0,0,Mid([mynumber]-[mynumberleft],3,1));

Not back in till Monday so hope this helps.

Brian