make +ve number -ve and -ve number stay same in same column

[Guest]

Budget is our income and sometimes this is positive. I need to make this
column in a query all negitive values.

I am using the IIf function but it is not working.

Budget: [budget_amount] IIf ([budget_amount] >=0, [budget_amount]*-1,
[budget_amount])

or is there another function I could use?

 

[Rob Parker]

Hi Tamm,

An iif statement will work - you've got the field in there twice:
Budget: IIf ([budget_amount] >=0, [budget_amount]*-1, [budget_amount])

Alternatively (and perhaps quicker) you could use:
Budget: -Abs([budget_amount])

HTH,

Rob

 

[Guest]

Thanks Rob

but still it does not work.

I dont know whats wrong.

I deleted what i had in the expression builder, coppied what you wrote and
pasted it directly in expression builder. for the "Field" row.

ERROR: "Extra ) in query expression 'dbo_jobcactb.[-Abs([budget_amount])].'

(NOTE: "dbo_jobcactb" is the table that "Budget" comes from and the outside
[] is put in the error itself).

I have tried deleting one and both the () and [] but still does not help.

Am i missing something somewhere?

Hi Tamm,

An iif statement will work - you've got the field in there twice:
Budget: IIf ([budget_amount] >=0, [budget_amount]*-1, [budget_amount])

Alternatively (and perhaps quicker) you could use:
Budget: -Abs([budget_amount])

HTH,

Rob

Budget is our income and sometimes this is positive. I need to make this
column in a query all negitive values.

I am using the IIf function but it is not working.

Budget: [budget_amount] IIf ([budget_amount] >=0, [budget_amount]*-1,
[budget_amount])

or is there another function I could use?

 

[Guest]

I was trying to do all the calulations within the column that the data was
in. for some reason access didnt like this.

Workaround: i created another column next to the budget column and used:
Budget_2: -Abs([Budget])

Thanks Rob

but still it does not work.

I dont know whats wrong.

I deleted what i had in the expression builder, coppied what you wrote and
pasted it directly in expression builder. for the "Field" row.

ERROR: "Extra ) in query expression 'dbo_jobcactb.[-Abs([budget_amount])].'

(NOTE: "dbo_jobcactb" is the table that "Budget" comes from and the outside
[] is put in the error itself).

I have tried deleting one and both the () and [] but still does not help.

Am i missing something somewhere?

Hi Tamm,

An iif statement will work - you've got the field in there twice:
Budget: IIf ([budget_amount] >=0, [budget_amount]*-1, [budget_amount])

Alternatively (and perhaps quicker) you could use:
Budget: -Abs([budget_amount])

HTH,

Rob

Budget is our income and sometimes this is positive. I need to make this
column in a query all negitive values.

I am using the IIf function but it is not working.

Budget: [budget_amount] IIf ([budget_amount] >=0, [budget_amount]*-1,
[budget_amount])

or is there another function I could use?