trouble with code for calculated value

[robinsong]

Hi, I am trying to figure out what's wrong with my code for a calculated value. I have a database for nesting birds, and I'm trying to get Access to return a value from various other fields, depending on which of those fields has data entered. Specifically, I have this code:



IIf([nest_fail_date] Is Null, Null, IIf(Or([cause_initial_egg_loss_specific] Is Null, [cause_initial_egg_loss_specific] Is Not Null), IIf([cause_subsequent_egg_loss_specific] Is Null, [cause_initial_egg_loss_specific], [cause_subsequent_egg_loss_specific])))

The error message is "The expression contains invalid syntax. You may have entered a comma without a preceding value or identifier." What am I doing wrong?? Thanks in advance for any input, much appreciated!!

[CJ_London]

IIf(Or([cause_initial_egg_loss_specific] Is Null, [cause_initial_egg_loss_specific] Is Not Null)

this is excel syntax. In access it would be

IIf([cause_initial_egg_loss_specific] Is Null OR [cause_initial_egg_loss_specific] Is Not Null,.....

but not clear why you are testing this anyway, [cause_initial_egg_loss_specific] is either null or not null, it can't be anything else

perhaps if you explained what you are trying to do, we can provide more focused help

[JoeM]

I think you have confused the OR function syntax with Excel's. In Access, it is just:

IIF(Condition1 OR Condition2,...)

[Missinglinq]

First off...

[cause_initial_egg_loss_specific] Is Null

isn't valid syntax, in Access VBA...it needs to be

IsNull([cause_initial_egg_loss_specific])

Linq ;0)>

[CJ_London]

good point - I'd assumed it was sql, but the error message would have been different

[robinsong]

Thank you for your responses. I was going off someone else's code and I think there were errors. I played around with it and finally came up with this, which worked to return what I needed in all instances:

IIf([cause_initial_egg_loss_general] Is Null, Null, IIf([cause_subsequent_egg_loss_specific] Is Not Null, [cause_subsequent_egg_loss_specific], [cause_initial_egg_loss_specific]))

Thanks again!!

[CJ_London]

keeping the field names short and in English your formula reads

if initialgeneral is null then return null otherwise if subsequentspecific is not null then return subsequentspecific otherwise return initialspecific

you could write this more simply as

iif(initialgeneral is null, null, nz(subsequentspecific,initialspecific))