Iff and Dlookup - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Iff and Dlookup

Chandra Wagner

13 months ago

I have a list of 1096 Plaintiffs with their dates of birth [DOB], and if they have passed, their dates of death [DOD] saved in tblPlaintiff. I want to calculate their ages, and if they are deceased their age at their time of death. Because the age if alive calculation will change daily (based on the =Date() function), I created a separate query, PlaintiffAgeQ that calculates the separate fields.

Now, I would like to store the age of a Plaintiff on the frmPlaintiff if they are alive and if they are deceased, that same text box would show their age at the time of their death. I can get the DLookup calculations to work in separate text boxes, but am running into an issue trying to nest the IIF and DLookup together in a single text box.

Any assistance would be much appreciated!

	Chandra Wagner OP @Reply 13 months ago

	Chandra Wagner OP @Reply 13 months ago

	Sandra Truax @Reply 13 months ago
Try this. FieldName: IIf([DOD] Is Null,DLookUp("AgeAtDeath","qryPlaintiffAge","PlaintiffID=" & "PlaintiffID"),DLookUp("AgeAlive","qryPlaintiffAge","PlaintiffID=" & "PlaintiffID"))

	Sandra Truax @Reply 13 months ago
That is actually backwards. Try this: FieldName: IIf([DOD] Is Null,DLookUp("AgeAlive","qryPlaintiffAge","PlaintiffID=" & "PlaintiffID"),DLookUp("AgeAtDeath","qryPlaintiffAge","PlaintiffID=" & "PlaintiffID"))

Donald Blackwell @Reply

13 months ago

You could also adjust your query to a single field and not need the Iif or DLookup(s) at all which would speed up your database when you get a lot of records:

Age: Int((Nz([DOD],Date())-[DOB])/365.25)

The Nz (Null Zero) function says if there is a Date-of-Death [DOD], use that, otherwise, use the current date (Date()).

If you only use this number on one form, you could also replace "Age:" above with "=" and just put it into the control source of the Plaintiff Age on PlaintiffF

	Chandra Wagner OP @Reply 13 months ago
Thanks Sandra and Donald for the responses. I used Donald's snytax and it worked, thanks (again), Donald. I am just starting the Expert Lvl 10 lessons and I noticed that the NZ function was coming up :D

	Donald Blackwell @Reply 13 months ago
Chandra Oops, sorry, forgot to look at your completion level. I should have been more considerate of that in my response.

	Richard Rost @Reply 13 months ago
And remember... it's IIF, not IFF. I'm going to leave your original subject as is in case people search for it. :)

	Chandra Wagner OP @Reply 12 months ago
Apologies for the typo in the subject line :/

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View