Access db query

Question

I'm having a challenge with the following string:

Datedeath: IIf([Members.Status]=[“DA”] Or [“DB”] Or [“DJ”] Or [“DC”], else [members.deathdate] "29/02/1844"

Might someone be able to guide me as to how to write this iif statement please?

yes. If the members status is one of the following status indicated, da, db and so on AND the deathdate field is blank then the deathdate field will fill with 29/02/1844. — Trish Main
– Trish Main, Commented Aug 22, 2014 at 4:06

ydoow · Accepted Answer · 2014-08-22 10:17:02Z

1

Check if the condition fulfilled, set new date or leave it

[members.deathdate]:IIf ((([Members.Status] = "DA"  Or [Members.Status] = "DB" Or [Members.Status] = "DJ" Or [Members.Status] = "DC") And ([members.deathdate] = "" Or IsNull([members.deathdate]))), "29/02/1844", [members.deathdate])

edited Aug 22, 2014 at 10:17

answered Aug 22, 2014 at 4:10

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trish Main Over a year ago

Thank you for your assistance. I received the following error report: The expression you entered has a function containing the wrong number of arguments.

user20416 Over a year ago

1) Shouldn't the check be for IS NULL instead of =""? 2) Why do you have square brackets around the string literals -- ["DC"] instead of "DC"? 3) The parentheses around the blank check is unnecessary. 4) How did you get smart quotes in your expression?

user20416 Over a year ago

If DeathDate is a Date/Time then there is no need to check for an empty string, just for nullity. Also, the expression I had in mind is AND Members.DeathDate IS NULL, which is more efficient as it uses an SQL expression instead of the VBA-exposed IsNull function.

Collectives™ on Stack Overflow

Access db query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related