Updating text field to new numeric field & moving minus sign to fr
in [Queries]
|
Prev: Obtaining the correct results from a two table query
Next: Access 2003: Select Next Item in Combobox
From: RyNC on 19 May 2010 16:56 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
From: Dorian on 19 May 2010 17:42
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 > |