Error Due to Missing Data - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Error Due to Missing Data

Marcelino Sanchez

14 months ago

Guys I need help with a PersonListF (form) I added to my Genealogy Database. The source of this List is a query (PersonListQ). I am using the following calculated field to determine the persons age at death based on their BirthDate & DeathDate:

AgeAtDeath: DateDiff("yyyy",[BirthDate],[DeathDate])+(DateSerial(Year([DeathDate]),Month([BirthDate]),Day([BirthDate]))<[DeathDate])

This code works fine; however, the problem I am having is the #Error that shows up in the AgeAtDeath field when a record is missing birth and death dates. I have looked through multiple videos and gone over my notes and can't seem to find a resolution. How can I hide the #Error for records missing dates.

Thank you in advance for your time.

	Kevin Robertson @Reply 14 months ago
IIF Function

Marcelino Sanchez OP @Reply

14 months ago

Kevin, thanks for your reply. I took another look at the IIF Function video, but I am still getting the same #Error results in my AgeAtDeath field due to missing BirthDate and DeathDate in some records. This database was designed by our instructor Richard. The Query consist of 2 tables (PersonT & GenderT). The query consist of the * from the PersonT, the GenderName from GenderT and the following Custom fields:

BirthDate: DLookUp("LifeEventDate","LifeEventT","PersonID=" & [PersonID] & " AND LifeEventTypeID=1")

DeathDate: DLookUp("LifeEventDate","LifeEventT","PersonID=" & [PersonID] & " AND LifeEventTypeID=2")

AgeAtDeath: DateDiff("yyyy",[BirthDate],[DeathDate])+(DateSerial(Year([DeathDate]),Month([BirthDate]),Day([BirthDate]))<[DeathDate])

When I Run the Query, I get the following results, since I am missing one of two dates or both in some cases:

BirthDate: Blank (Good) | DeathDate: Blank (Good) | AgeAtDeath: #Error (Not Good - Want to fix this.)

	Adam Schwanz @Reply 14 months ago
Where do you run the IIF function? You should be able to do this `IIF(IsNull(BirthDate) or IsNull(DeathDate),"Put Null or 0 without quotes or Empty String Here Whatever you want if One or Both Are Null",DateDiff...`

	Marcelino Sanchez OP @Reply 14 months ago
Thank you so much Adam Schwanz. This was exactly what I needed to resolve my issue. The query is now prodcing the correct data without errors for my PersonListF (form). Greatly appreciate your time and the code snippet you provided. God bless you.

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

Next Unseen

New Feature: Comment Live View