Table Structure:

Table: Siblings
SiblingID (PK)
ContactID (FK)
SiblingDOB
LastName
FirstName
SibSex
SibRace
EntryDate
CalculatedAge
Assessment (Is has to be calculated behind the scenes with an If/Then/
Else statement)
AssessmentComplete
Blah1
Blah2
Blah3

What I need to do:

1) I need to calculate their age using SiblingDOB and EntryDate behind
the scenes and store it in CalculatedAge.
2) I need to take the calculate age and create groups and store it in
Assessment. So
a. If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
b. If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
c. If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
d. Else = Error

How do I do this in access?

Thanks!

On Jul 28, 11:55*am, Craig <cjohn...@gmail.com> wrote:
> Table Structure:
>
> Table: Siblings
> SiblingID (PK)
> ContactID (FK)
> SiblingDOB
> LastName
> FirstName
> SibSex
> SibRace
> EntryDate
> CalculatedAge
> Assessment (Is has to be calculated behind the scenes with an If/Then/
> Else statement)
> AssessmentComplete
> Blah1
> Blah2
> Blah3
>
> What I need to do:
>
> 1) * * *I need to calculate their age using SiblingDOB and EntryDate behind
> the scenes and store it in CalculatedAge.
> 2) * * *I need to take the calculate age and create groups and store it in
> Assessment. *So
> a. * * *If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
> b. * * *If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
> c. * * *If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
> d. * * *Else = Error
>
> How do I do this in access?
>
> Thanks!

You don't. Table fields should almost never contain calculated data.
If you need to have access to those two pieces of data, use a query to
calculate them and use the query as the data source for anything that
needs to interact with it.

Keven Denen

On Jul 28, 12:03*pm, Keven Denen <keven.de...@gmail.com> wrote:
> On Jul 28, 11:55*am, Craig <cjohn...@gmail.com> wrote:
>
>
>
> > Table Structure:
>
> > Table: Siblings
> > SiblingID (PK)
> > ContactID (FK)
> > SiblingDOB
> > LastName
> > FirstName
> > SibSex
> > SibRace
> > EntryDate
> > CalculatedAge
> > Assessment (Is has to be calculated behind the scenes with an If/Then/
> > Else statement)
> > AssessmentComplete
> > Blah1
> > Blah2
> > Blah3
>
> > What I need to do:
>
> > 1) * * *I need to calculate their age using SiblingDOB and EntryDate behind
> > the scenes and store it in CalculatedAge.
> > 2) * * *I need to take the calculate age and create groups and store it in
> > Assessment. *So
> > a. * * *If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
> > b. * * *If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
> > c. * * *If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
> > d. * * *Else = Error
>
> > How do I do this in access?
>
> > Thanks!
>
> You don't. Table fields should almost never contain calculated data.
> If you need to have access to those two pieces of data, use a query to
> calculate them and use the query as the data source for anything that
> needs to interact with it.
>
> Keven Denen

To calculate those in a query...

In a new column do:
CalculatedAgeateDiff("yyyy", [SiblingDOB], Now())+ Int( Format(now
(), "mmdd") < Format( [SiblingDOB], "mmdd") )

then in another new column do:
Assessment: IIf([CalculatedAge]>=3 And [CalculatedAge]<=5,"Test 1",IIf
([CalculatedAge]>=6 And [CalculatedAge]<=12,"Test 2",IIf
([CalculatedAge]>=13 And [CalculatedAge]<=18,"Test 3","Error")))

Keven Denen

You don't. What happens next year when the person is a year older, but the
table hasn't been updated? What happens when someone finds a mistake in the
SiblingDOB field and fixes it, but forgets to update the calculated fields?

There are a few exceptions to the rule about saving calculated data, but
this probably isn't one of them. What you want to do is calculate the age
WHEN you need it. Same goes with the Accessment.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Craig" wrote:

> Table Structure:
>
> Table: Siblings
> SiblingID (PK)
> ContactID (FK)
> SiblingDOB
> LastName
> FirstName
> SibSex
> SibRace
> EntryDate
> CalculatedAge
> Assessment (Is has to be calculated behind the scenes with an If/Then/
> Else statement)
> AssessmentComplete
> Blah1
> Blah2
> Blah3
>
> What I need to do:
>
> 1) I need to calculate their age using SiblingDOB and EntryDate behind
> the scenes and store it in CalculatedAge.
> 2) I need to take the calculate age and create groups and store it in
> Assessment. So
> a. If calculatedage >=3 & calculatedage <=5 then Assessment = Test 1
> b. If calculatedage >=6 & calculatedage <=12 then assessment = Test 2
> c. If calculatedage >=13 & calcualtedage <=18 then assessment = Test 3
> d. Else = Error
>
> How do I do this in access?
>
> Thanks!
>

I completely agree with you that this isn't what you normally do,
however, in this circumstance we want it calculated and we want it
stored. Here's why.....

The data being stored is for a large research study and the data will
not change, we are only collecting this info ONCE. If you look back
at my original email you'll notice that the calculated age is not
based on the current date but rather the date entered (static) so the
concept of it changing overtime is invalid. The assessment that is
calculated is based on the static age and it will remain static. What
you have for these two pieces of information is basically a lookup
table so that we can calculate their age and then calculate what test
they should take and store it. I want to use this two step process
(calculate age -> determine assessment) to eliminate user error of
calculating age and then figuring out which assessment they should
take. I'm then going to use the stored data of what assessment that
they took as a data check against the physical form they are going to
fill out. I don't want people figuring this stuff out themselves (I
need to make it simple and calculate it all out for them) and I want
it stored so that it's static data. That way we can verify the data
against data that will be scanned in from paper forms (IE does the
form they should have gotten match what we actually received) and can
use this information for data verification purposes for statistical
analyses.

Long story short......it's complicated, I get using queries and it
would be a lot easier, but I don't think it's going to work in this
instance. The frustrating thing is I could do this in about 2 minutes
if I thought a query would work (maybe a triggered update since it
will always be entered through forms?) or I could do it in SAS or
Excel but all the info is stored in Access so I need to find a
solution. I know I'm not the first person who's had to do this.....I
guess I'll keep digging.

So if you feel you must do this then use the Before UPdate event of the entry
form to populate those "calculated" values using the expressions Keven Denen
posted.

CalculatedAge = DateDiff("yyyy", [SiblingDOB], EntryDate)+ Int(
Format(EntryDate, "mmdd") < Format( [SiblingDOB], "mmdd") )

and then
Assessment = IIf([CalculatedAge]>=3 And [CalculatedAge]<=5,"Test 1",IIf
([CalculatedAge]>=6 And [CalculatedAge]<=12,"Test 2",IIf
([CalculatedAge]>=13 And [CalculatedAge]<=18,"Test 3","Error")))

Or you can use the after update event of the two controls - SiblingDOB and
EntryDate - to check to see if they are filled in and if they are set two
controls on the form to the values.

Be warned that all data entry must be through forms OR you must run an update
query to populate the fields if you import data from other sources.

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

Craig wrote:
> I completely agree with you that this isn't what you normally do,
> however, in this circumstance we want it calculated and we want it
> stored. Here's why.....
>
> The data being stored is for a large research study and the data will
> not change, we are only collecting this info ONCE. If you look back
> at my original email you'll notice that the calculated age is not
> based on the current date but rather the date entered (static) so the
> concept of it changing overtime is invalid. The assessment that is
> calculated is based on the static age and it will remain static. What
> you have for these two pieces of information is basically a lookup
> table so that we can calculate their age and then calculate what test
> they should take and store it. I want to use this two step process
> (calculate age -> determine assessment) to eliminate user error of
> calculating age and then figuring out which assessment they should
> take. I'm then going to use the stored data of what assessment that
> they took as a data check against the physical form they are going to
> fill out. I don't want people figuring this stuff out themselves (I
> need to make it simple and calculate it all out for them) and I want
> it stored so that it's static data. That way we can verify the data
> against data that will be scanned in from paper forms (IE does the
> form they should have gotten match what we actually received) and can
> use this information for data verification purposes for statistical
> analyses.
>
> Long story short......it's complicated, I get using queries and it
> would be a lot easier, but I don't think it's going to work in this
> instance. The frustrating thing is I could do this in about 2 minutes
> if I thought a query would work (maybe a triggered update since it
> will always be entered through forms?) or I could do it in SAS or
> Excel but all the info is stored in Access so I need to find a
> solution. I know I'm not the first person who's had to do this.....I
> guess I'll keep digging.
>
>
>
>
>
>

I had actually just started playing around subs. I tried some of the
supplied code (see below for actual field names) and it's throwing an
error. I'm sure it is probably something simple I am missing but if
you happen to notice something wrong let me know. For what it's
worth, both of those fields are date fields and both fields are
standard access date format (mmddyy).

*******
Code
*******
Private Sub SibDOB_AfterUpdate()
Me.CalculatedAge = DateDiff("yyyy", [Me.SibDOB], Me.DateEntered) +
Int(Format(Me.DateEntered, "mmdd") < Format([Me.SibDOB], "mmdd"))
End Sub

*******
Error
*******
Run time error '2465'; Database can't find the field '|' referred to
in your expression.

Actually, I I found a post on another forum that said to remove the
brackets and now it works.....time for the Assessment!

On Jul 28, 2:44*pm, Craig <cjohn...@gmail.com> wrote:
> Actually, I I found a post on another forum that said to remove the
> brackets and now it works.....time for the Assessment!

Ya, I wrote it as you would if it were in the query builder, not in
code.

Keven Denen

You have brackets [] around Me.SibDOB


Private Sub SibDOB_AfterUpdate()

'Check to see if you have values in the two controls.
If IsDate(me.DateEntered) and IsDate(me.SibDOB) then

Me.CalculatedAge = DateDiff("yyyy", Me.SibDOB, Me.DateEntered) +
Int(Format(Me.DateEntered, "mmdd") < Format(Me.SibDOB, "mmdd"))

END IF

End Sub


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


Craig wrote:
> I had actually just started playing around subs. I tried some of the
> supplied code (see below for actual field names) and it's throwing an
> error. I'm sure it is probably something simple I am missing but if
> you happen to notice something wrong let me know. For what it's
> worth, both of those fields are date fields and both fields are
> standard access date format (mmddyy).
>
> *******
> Code
> *******
> Private Sub SibDOB_AfterUpdate()
> Me.CalculatedAge = DateDiff("yyyy", [Me.SibDOB], Me.DateEntered) +
> Int(Format(Me.DateEntered, "mmdd") < Format([Me.SibDOB], "mmdd"))
> End Sub
>
> *******
> Error
> *******
> Run time error '2465'; Database can't find the field '|' referred to
> in your expression.
>