Access DB calc using the an IF function

[copperwasher]

I have a simple designed Access Database, but I have a challenge.



I need to create an expression that can provide an answer based upon the select of Buy or Sell column…

The Expression used in the 'difference column'
[Current Price]-[Price Entry]

If Buy is selected, the calc (in the difference column) should be [Current Price]-[Price Entry]

If Sell is selected, the calc should be [Price Entry]- [Current Price]

But how do I write the expression in the expression builder, so it uses the right calc for a Buy or Sell trade ?



The table should work as below...
Currently the sell trade calc is wrong and working the same as a buy trade

Buy/Sell	price entry	current price	Difference	winning/losing
Buy	1.00200	1.00300	0.00100	Winning
Buy	1.00200	1.00100	-0.00100	Losing

Buy/Sell	price entry	current price	difference	winning/losing
Sell	1.00200	1.00100	0.00100	Winning
Sell	1.00200	1.00300	-0.00100	Losing

Any guidance is greatly appreciated



CW

 

[Micron]

I'm going to assume you want an expression using IIF - If isn't a function in Access. Also will presume that if buy/sell is not one it has to be the other 1 alternative.

IIF([Buy/Sell]="Buy",[Current Price]-[Price Entry], [Price Entry]-[Current Price])

If you are storing these calculations you should not; best to do so only on forms or in queries. Also should not use special characters in object names ( / ) nor spaces.

 

[Norie]

You can try something like this.

Difference : Iif([Buy/Sell]='Buy',  [Current Price]-[Price Entry],  [Price Entry]- [Current Price])

 

[copperwasher]

I'm going to assume you want an expression using IIF - If isn't a function in Access. Also will presume that if buy/sell is not one it has to be the other 1 alternative.

IIF([Buy/Sell]="Buy",[Current Price]-[Price Entry], [Price Entry]-[Current Price])

If you are storing these calculations you should not; best to do so only on forms or in queries. Also should not use special characters in object names ( / ) nor spaces.

Hi Mircron,

Sorry, my bad. I thought I was being thorough enough, clearly not...

Yep, I used the IF as there are just two options BUY or SELL, I thought that was a good way to describe the set-up

Storing:
I am not to conversant with Access.
There are a host of Queries (26), Forms (2), Reports (11) for the database.

Should I change or amend any headings, removing any special characters ?

 

[copperwasher]

You can try something like this.

Difference : Iif([Buy/Sell]='Buy',  [Current Price]-[Price Entry],  [Price Entry]- [Current Price])

Hi Norie,

Thanks for the expression.

Will I also have potential storage issues with your code ?

I am not to conversant with Access. There are a host of Queries (26), Forms (2), Reports (11) for the database.
Should I change or amend any headings, removing any special characters ?

Just apply the code and fully testing...

 

[Micron]

Should I change or amend any headings, removing any special characters ?

Not in your currently working db but maybe in a copy that you can switch to later if successful. Or just research naming conventions for the next one.
Your post suggests you didn't notice that I presented practically the same expression..