Creating an Update Query

[Bongard]

I am trying to run an update query (let me know if this is a better
way) to Update the [Amount] field in a table tbl_Transactions. I want
to multiply the [Amount] by -1 if another field in the table
[Direction] = "Outbound" Currently I have an If statement that looks
like this:

IIf([Direction]="Outbound",[Amount]*-1,[Amount])

I can get this to calculate correctly in a view(select) query but I
don't know how to get this to work in the Update query.

I'm hoping that this is very easy for someone but I can't figure it out
so thanks in advance!

-Brian

 

[kingston via AccessMonster.com]

Your IIF statement looks correct. Are you not getting the result that you
want when you run the query? You will not see the results when viewing the
query in select mode. Every time you run the query, the data will be
modified so you should only do this once. If you mean that you want the
amount to always be negative for an outbound transaction, try ABS([Amount])*-
1. HTH.

I am trying to run an update query (let me know if this is a better
way) to Update the [Amount] field in a table tbl_Transactions. I want
to multiply the [Amount] by -1 if another field in the table
[Direction] = "Outbound" Currently I have an If statement that looks
like this:

IIf([Direction]="Outbound",[Amount]*-1,[Amount])

I can get this to calculate correctly in a view(select) query but I
don't know how to get this to work in the Update query.

I'm hoping that this is very easy for someone but I can't figure it out
so thanks in advance!

-Brian

 

[Bongard]

Good point with ABS function - that will need to be used as well so
that I am not switching the amount back and forth from positive to
negative everytime the query is run.

The query does work as a select query but it does not work when I enter
this IIf statement into the "Update To" field of the Update Query. When
I preview the Update Query I get the exact same values as the field
[Amount] already contains. Its like the multiplication is not taking
place. I am not getting an error saying that my syntax is incorrect or
anything - It just doesn't seem to work.

Thanks for your help man!

-Brian

 

[kingston via AccessMonster.com]

Preview only shows the original data AFAIK. The data will only change once
the update query has been run.

Good point with ABS function - that will need to be used as well so
that I am not switching the amount back and forth from positive to
negative everytime the query is run.

The query does work as a select query but it does not work when I enter
this IIf statement into the "Update To" field of the Update Query. When
I preview the Update Query I get the exact same values as the field
[Amount] already contains. Its like the multiplication is not taking
place. I am not getting an error saying that my syntax is incorrect or
anything - It just doesn't seem to work.

Thanks for your help man!

-Brian

 

[Bongard]

Thanks, this helps - THe preview is what I was having a problem with.
This now works!


-Brian