Formula if then with return from column value

[katie07]

I need help
I need to calculate field based on the following criteria:
If Historical Value=0, then return $value from field acquired value, if not return $value from historical value field. How can I do that?

 

[MajP]

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

iif([historical] = 0,[acquired],[historical])

 

[katie07]

Its not working;( It only returns value from Historical Value field if it doesn't equal 0, but not picking up value from Acquired Value field when historical does =0.

 

[katie07]

Expr1: IIf([Historical Value]=0,[Acquired Value],[Historical Value])

 

[katie07]

I think something is wrong with my data which I imported from excel. The formula works when I open a new access doc and just enter data manually, but it doesn't work with imported data. I have 6k rows so I wouldn't be able to enter everything manually. Do you have an idea how to fix the problem?

 

[MajP]

Is that field a numeric field? If it is a text field then you might see 0 but there are other characters not visible. What is the fields datatype?

 

[katie07]

OMG I figured it out! Thank you so much!!!!!!

 

[katie07]

The field had no value in it like $0.00, that's why it was not picking up.... thanks again! I never worked in access so this project is killing me, but its good to learn something new

 

[MajP]

A blank empty field in Access usually has a value of null. To check if something is null you can use the function
isNull()
This returns true or false if it is null. You can convert a null to something else using the NZ function
nz(value,0)
So you can convert a null into 0 or whatever you want.
You can never check if something is null using boolean logic
if SomeField = Null
The above is never true even if the field is null because Null = Null returns false.

So if Historical value can be null or 0 and you want to check for both then
iif(nz([historical value],0) = 0