Prev: Calculating Elapsed Time
Next: delete query
From: John Spencer on 20 May 2010 08:41
Just for your future information as long as the field is not null you can use
CDbl.

CDbl("23.32-") returns -23.32

If you want to be careful you can use

UPDATE [MyTable]
SET [MyTable].[MyNumericField] =
IIF(IsNumeric([MyTextField]),CDbl([MyTextField]),Null)

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