Create a new field from calculation

[heathb]

Hi Guys, very rusty bod here so please bare with me

I have a query that maps a few tables together for a single output, as usual

I have a field "Net position" that I want to drive a new field so if <0 then S or >0 then B

in table design I have a new field name "Buy_Sell" with a calculated data type IIf("Net_Position"<0,"S","B")

however, when I run my query i get a result of #Type!


Please help

Thanks

 

[pbaldy]

Does the field show correctly in the table in datasheet view? I would expect something more like:

IIf([Net_Position]<0,"S","B")

note the brackets instead of quotes around what I assume is a field name.

 

[heathb]

ok, that worked, obviously cheers.

however, even saving the table, when I re run the query that field disappears,

The query is make table and that deletes and re adds the table, have tried to add Expr1: [Buy_Sell] to the query but this brings up a input field.
Have also tried to change it to update but told need at least one destination field.
screen shots attached hopefully

 

[heathb]

some more screen shots

 

[Wehbyj]

I Guess you have to change the field name above,
from :
Expr1: [Buy_Sell]
with your criteria
to : Bus_Sell: IIF([Net Position]<0,"S","B")
without any criteria.

that's if I understood your post well.

 

[pbaldy]

Yes, though I thought you meant you had a table-level calculated field. Post 5 would certainly work in a query, and correct, it should be in the field, not the criteria.

 

[heathb]

Hi Wehbyj & pbaldy

Thanks, this is working really well. One last question hopefully.

Please see attached, why am i getting a "Enter parameter value" for Net position??

 

[Wehbyj]

The query says : make a field named "Buy_Sell" with a value based on the value of "Net Position" parameter. So if [Net position] is not defined somewhere it will ask you for it as you got.
Usually, It must be a field of the table your query based on.
I guess you have miss-typed its name, so use [Net_Position].

 

[heathb]

thanks for the help guys, enjoy your weekend