Removing first character IF

[gtroiano]

i am trying to make an update query that will remove a negative sign(-) from in front of a string if it is present and add a negative sign(-) to the front of the string if there is not one present. thanks.

jerry.

 

[bustell]

Use this for the SQL Statement:

SELECT IIf(Left([FieldName],1)="-",Right([FieldName],Len([FieldName])-1),"-" & [FieldName]) AS Result
FROM tblTableName;

Or in the query builder window use this for one of the columns:

Result: IIf(Left([FieldName],1)="-",Right([FieldName],Len([FieldName])-1),"-" & [FieldName])

Pat B

 

[gtroiano]

that gets the information i need but i need it to actually update the table. sorry, but my SQL knowledge is bad. any ideas?

jerry.

 

[bustell]

Create a query. Add the field you want to update. Change it to an update query (click on query and then update on the menubar). Paste the following in the "Update To" cell on the grid for the field selected:

IIf(Left([YourFieldName],1)="-",Right([YourFieldName],Len([YourFieldName])-1),"-" & [YourFieldName])

This will permenently update the records for you.
Pat B

 

[gtroiano]

perfect. worked like a charm.
thanks a lot.

jerry.