Problem in function in calculated field

[CosmaL]

Hello everybody!!!

Can anybody help me with this please?

I have these function as calculated field in a table (ms access 2013)

After setting up the function, access shows error for invalid dot or operator or unexisted field (field exists )

iif([MoistureContent]>9 or <4;round(0.636+3.12*exp(0.346*[MoistureContent]);2);((-0.133*[MoistureContent]+1.96)*[Test1PerforatorValue]))

I've also tried to break the iif statement using another iif instead of or but i always get the same error.

I've checked step by step, but in the end the error is there!

Aby ideas will be appreciated!

Thank you in advance!!!!!!

 

[pr2-eugin]

CosmaL, do not store calculations in Tables. You will end up in a lot more complication as you progress. http://allenbrowne.com/casu-14.html

 

[namliam]

The syntax of your IIF if faulty
iif([MoistureContent]>9 or <4
That will not work, instead
iif([MoistureContent]>9 or [MoistureContent]<4

And are you sure about the ; shouldnt that be , ?

 

[CosmaL]

Paul,
i'd like to have it calculated in the table before running the query to report samples analysis. (database is about samples analysis)
As alternative i could have a textbox on report and make calculations on the fly, but there are 6-8 fields with calculations, rest are working fine!

Namliam,
i used comma but access dropped it, it asked forr ; (i've doubled checked it using iif syntax and it's iif(expression;truepart;falsepart)

 

[namliam]

paul is right though, calculations like these should "just" be done on query basis, not be stored in a table.

, vs ; depends on your regional settings, so either is possible depending on your own situation.

 

[CosmaL]

I'll try to make it on the fly and not store it in table, i'll have it ready propably tomorrow. I'll update the post!

Thanks guys!!!

 

[pr2-eugin]

Paul,
i'd like to have it calculated in the table before running the query to report samples analysis. (database is about samples analysis)
As alternative i could have a textbox on report and make calculations on the fly, but there are 6-8 fields with calculations, rest are working fine!

More reasons not to store this information ! Calculations belong in Queries, not in Tables. Any calculation that you wish to perform, can be and should be done in Queries/Reports.

Namliam,
i used comma but access dropped it, it asked forr ; (i've doubled checked it using iif syntax and it's iif(expression;truepart;falsepart)

I think you have a regional setting that uses semi-colon as the separator. Although I think namliam showed the correct syntax to use Or in the IIF.

 

[CosmaL]

More reasons not to store this information ! Calculations belong in Queries, not in Tables. Any calculation that you wish to perform, can be and should be done in Queries/Reports.
I think you have a regional setting that uses semi-colon as the separator. Although I think namliam showed the correct syntax to use Or in the IIF.

I'll correct it, i'll make calculations on the report, query will just collect sample information and result values.
Regional setings are in greek, this is propably the reason that it works with semi-colon.
I've already change the syntax of the iif statement!

I'll update the post tomorrow!

 

[namliam]

On average doing calculations inside queries is the best idea, as that makes them more easily and more genericaly re-usable in different reports or forms, instead of having to maintain the same formula in different forms and/or reports.

 

[CosmaL]

Thanks guys! i did a test on report with the function and worked!

Thanks!!!!!!!!!!!!!!!!!