From: John Spencer on
Just for your future information as long as the field is not null you can use

CDbl("23.32-") returns -23.32

If you want to be careful you can use

UPDATE [MyTable]
SET [MyTable].[MyNumericField] =

Obviously, if you want zero or some other number result in MyNumericField when
MyTextField cannot be interpreted as a number you can replace null with zero
or -999999 (or whatever value you want).

OR you could use
UPDATE [MyTable]
SET [MyTable].[MyNumericField] = CDbl([MyTextField])
WHERE IsNumeric(MyTextField) = True

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
> CLng is dropping the fractional part.
> Try using CDbl ?
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
> "RyNC" wrote:
>> Hi,
>> I need some help with the below IF statement for update query. I got it to
>> move the negative sign to the front of the number, but now it is removing all
>> numbers after my decimal point. I've checked to make sure my formatting for
>> [MyNumericField] is double and 2 decimal points.
>> For example:
>> [MyTextField] is 23.32-
>> I run update query and....
>> [MyNumericField] is -23
>> Here is what I have for the update query:
>> UPDATE [MyTable] SET [MyTable].[MyNumericField] =
>> IIf(Right([MyTextField],1)="-",-CLng(Left([MyTextField],Len([MyTextField])-1)),CLng([MyTextField]))
>> Thanks,
>> RyNC
Pages: 1
Prev: Calculating Elapsed Time
Next: delete query