Convert Excel Formula to access

[weldtaha]

Hi All,

I never use access but have to create a field in a table with a formula in it. The formula works fine in excel. Does anybody know how to change it to access?

The Excel formula is:

=IF(RET.*Rate<=500,IF(J15>0,N15*L15*0.35*0.3,0),IF(J15>0,N15*L15*0.35*0.25,0))

Best Regards

Thanks

 

[Joe4]

Welcome to the Board!

In Access, you should create calculated field in Queries, not Tables. Newer version of Access allow you to do some basic calculation right at the table level, but it isn't really advised to do so (it isn't compatible with other database programs).

The formula in your query would look very similar, except use IIF instead of IF.

 

[xenou]

The J15, N15, L15 references would also have to be field names from the table. As Joe said, once you create the table with the base data, the calculation would normally be done in a query but with some exceptions you might save the results of a calculation in a table. The only common example I know of is storing a price that is the result of a lookup, because it's a value that might change in the future and you want to capture the current price at the moment and keep it for later use.