Problem with converting Text to Number

[Lance]

I am attempting to convert a text field into a long integer.

I have two fields in my table.

Field1 (Value COCurr): Text field containing string numbers with
commas, decimals, and trailing negative signs
Field2 (Cost): Long integer field containing nothing.

I am trying to run an update query, that looks at the string in field
1, determines whether it has a trailing negative sign or not, and then
attempts to convert it into a number, using the cLng conversion
function.

However, when I try to execute this update query, I get no value in
Field2. It's as if I've done nothing. What am I doing wrong?

I have included the SQL below...

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost =
CLng(if(Right([Value COCurr],1)="-",-Left([Value COCurr],Len([value
COCurr])-1),[Value COCurr]));

 

[John Spencer]

Try something simpler

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost = IIF(IsNumeric(Value
COCurr),CLng(Value COCurr),Null)

Your other query (as posted) used IF and not IIF. IF doesn't work in
queries.

Also, I assume you are aware that CLng will round the numbers using banker's
rounding.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lance]

John,

Thank you for your response.

The reason it is so complex is because I need those numbers with the
trailing zeros to convert to actual negative numbers. The IIF was an
oversight, that I will go check to see if that is the source of my
problem. CLng will work for what I am doing, but thank you for
pointing out the rounding - I did not know that.

Lance

Try something simpler

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost = IIF(IsNumeric(Value
COCurr),CLng(Value COCurr),Null)

Your other query (as posted) used IF and not IIF. IF doesn't work in
queries.

Also, I assume you are aware that CLng will round the numbers using banker's
rounding.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am attempting to convert a text field into a long integer.

I have two fields in my table.

Field1 (Value COCurr): Text field containing string numbers with
commas, decimals, and trailing negative signs
Field2 (Cost): Long integer field containing nothing.

I am trying to run an update query, that looks at the string in field
1, determines whether it has a trailing negative sign or not, and then
attempts to convert it into a number, using the cLng conversion
function.

However, when I try to execute this update query, I get no value in
Field2. It's as if I've done nothing. What am I doing wrong?

I have included the SQL below...

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost =
CLng(if(Right([Value COCurr],1)="-",-Left([Value COCurr],Len([value
COCurr])-1),[Value COCurr]));

 

[John Spencer]

But CLNG will convert the numbers correctly. For instance

Clng("123,001.02 -") returns -123001

So I see no reason for using your complex expression.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thank you for your response.

The reason it is so complex is because I need those numbers with the
trailing zeros to convert to actual negative numbers. The IIF was an
oversight, that I will go check to see if that is the source of my
problem. CLng will work for what I am doing, but thank you for
pointing out the rounding - I did not know that.

Lance

Try something simpler

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost = IIF(IsNumeric(Value
COCurr),CLng(Value COCurr),Null)

Your other query (as posted) used IF and not IIF. IF doesn't work in
queries.

Also, I assume you are aware that CLng will round the numbers using
banker's
rounding.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am attempting to convert a text field into a long integer.

I have two fields in my table.

Field1 (Value COCurr): Text field containing string numbers with
commas, decimals, and trailing negative signs
Field2 (Cost): Long integer field containing nothing.

I am trying to run an update query, that looks at the string in field
1, determines whether it has a trailing negative sign or not, and then
attempts to convert it into a number, using the cLng conversion
function.

However, when I try to execute this update query, I get no value in
Field2. It's as if I've done nothing. What am I doing wrong?

I have included the SQL below...

UPDATE [2007-09 A380 Production Hardware]
SET [2007-09 A380 Production Hardware].Cost =
CLng(if(Right([Value COCurr],1)="-",-Left([Value COCurr],Len([value
COCurr])-1),[Value COCurr]));