Combining three expressions together to create one expression

[Jennifer K.]

I have the following expressions:

GBIRTH: IIf([GDOB]=99 Or [GMOB]=99 Or
[GYOB]=9999,Null,DateSerial([GYOB],[GMOB],[GDOB]))

MDATE: IIf([DOO]=99 Or [MOO]=99 Or
[YOO]=9999,Null,DateSerial([YOO],[MOO],[DOO]))

MAGE:
DateDiff("yyyy",[GBIRTH],[MDATE])-IIf(Format([GBIRTH],"mmdd")>Format([MDATE],"mmdd"),1,0)

The first and the second expression take three separate fields representing
date "pieces" (day, month, and year) and put it into a date format.

The third expression calculates age based upon the two dates. They all work
well individually when used in a query.

I would like to combine this into one (long) expression that could ideally
be used in a form (or in a query) to calculate age once the two dates are
entered into the form. Likely, I am probably messing up a paren ( ) somewhere
but not sure.

Thanks you for your input.

Jennifer

 

[KARL DEWEY]

The problem is that Access can not calculate MAGE if it has not calculated
GBIRTH and MDATE first.
Use the same calulation in MAGE instead of GBIRTH and MDATE.

 

[Jeff Boyce]

Jennifer

Please post the expression you are trying to use. Do you get an error
message when you use it?

Have you considered creating a procedure that generates an Age based on a
DOB? Have you checked on-line (your favorite search engine or at
mvps.org/access)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

That is complicated enough that I would use a custom function and pass it the
six arguments. Especially since you should be testing for nulls and other out
of range values. UNTESTED function follows - paste it into a VBA module and
call it from whereever you need it.

Public Function fGetAge(BD, BM, BY, OD, OM, OY)

Dim DOB As Date, ODte As Date

If IsDate(BY & "-" & BM & "-" & BD) = False Or _
IsDate(OY & "-" & OM & "-" & OD) = False Then
fGetAge = Null
Else
DOB = DateSerial(BY, BM, BD)
ODte = DateSerial(OY, OM, OD)

fGetAge = DateDiff("yyyy", DOB, ODte) - _
IIf(Format(DOB, "mmdd") > Format(ODte, "mmdd"), 1, 0)
End If

End Function



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jennifer K.]

What I am finding is that this is beginning to feel very complicated.

Having given it some thought overnight, I am wondering if I should leave it
as the three separate expressions in a query and then somehow have the query
result show in my form?

Essentially, the person doing data entry needs to be able to know the age of
the individual being entered into the database. If they are under the age of
16 there is either a data entry mistake, or that individual is too young to
participate. Once I have created this database, it needs to be simple enough
for someone (other than myself!) to manage, so I am trying to keep it as
uncomplicated as possible and still do what they want it to do.

Is there a way that I can take a value from a query that is based on
information entered into the table (via the form) and have it show on the
form?

Thanks again
Jennifer