Calculated Formula on table not working

[ensmith]

I am trying to create the below excel formula on a access table and it is not working. Any suggestions, thanks.

Excel formula - =IF(ISBLANK(G2),F2,IF(G2="UNKNW",F2,G2))

	C	D	E	F	G
1	Year	Acct	Comp Code	BILL_CD	PAYOR_CD
2	2014	0000000	CM	MH34	UNKNW

<tbody>
</tbody>

Access table formula - IsNull( [PAYOR_CD] , [BILL_CD] , IF( [PAYOR_CD] ="UNKNW", [BILL_CD], [PAYOR_CD] ))

Year

Acct

Comp Code

BILL_CD

PAYOR_CD

<tbody>
</tbody>

 

[alansidman]

It is not advisable to calculate fields in database tables. All calculations should be performed in either a form or a query. Data is static in tables, and if some data were to change, then all of the calculations in the table would need to be re-calculated. This could be a large job. Keeping calculations in queries, allows them to be performed when the query is run and if data has changed, then the calculation is correct at the time of running.

Looking at your expression (database term for formula), there is no IF function, you need to use an IIF function.
In a query, you would create a new field and insert your expression there.

 

[ensmith]

It is not advisable to calculate fields in database tables. All calculations should be performed in either a form or a query. Data is static in tables, and if some data were to change, then all of the calculations in the table would need to be re-calculated. This could be a large job. Keeping calculations in queries, allows them to be performed when the query is run and if data has changed, then the calculation is correct at the time of running.

Looking at your expression (database term for formula), there is no IF function, you need to use an IIF function.
In a query, you would create a new field and insert your expression there.

Thanks for your advice.

 

[ensmith]

Can you tell me if I am something in this query formula because it is not working?

IIf(IsNull( [1 - NE Payor Detail]![PAYOR_CD] , [1 - NE Payor Detail]![BILL_CD] ),IIf( [1 - NE Payor Detail]![PAYOR_CD] ,"UNKNW", [1 - NE Payor Detail]![BILL_CD] ))

 

[Joe4]

You formula is all messed up. In addition to having parentheses in the wrong place, you also want to check to see if it is equal to "UNKNW", so you want an equal sign there, not a comma.

You don't need to do it as a nested IIF anyhow, you can just use OR, i.e.

[COLOR=#333333]IIf(IsNull([1 - NE Payor Detail]![PAYOR_CD]) OR ([1 - NE Payor Detail]![PAYOR_CD]="UNKNW"), [1 - NE Payor Detail]![BILL_CD],[/COLOR][COLOR=#333333][1 - NE Payor Detail]![PAYOR_CD][/COLOR][COLOR=#333333])[/COLOR]

 

[ensmith]

Thanks Joe4 this worked great. Thanks again.