UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Preserving A Calculated Age, Access 2016    
 
   
woopow
post Feb 24 2020, 02:16 PM
Post#1



Posts: 10
Joined: 27-June 03
From: Sleeping in the desert


Hey Folks,

I have a database of clients with a calculated field where the age is calculated using DateDiff of their DOB and today's date. This is the formula I use: =DateDiff("yyyy",[birthdate],Date())+(Format([birthdate],"mmdd")>Format(Date(),"mmdd"))
So long as the client is current, their age is current.

However if a client exits our program, the formula doesn't account for the lapse of time. For example, if I need to search a client who exited a program ten years ago, the Age field shows how old they are today, not when they were in our program ten years ago. I need it to stop calculating when the client exits our program.

In other words, is there a way to do an either/or with the Date() and an Exit Date...so that the system calculates an Age using [birthdate] and [Exit Date] OR if the [Exit Date] is null, it uses [birthdate] and Date()?

I hope I'm making sense. tongue.gif

Thanks!
Brenda
Go to the top of the page
 
moke123
post Feb 24 2020, 02:26 PM
Post#2



Posts: 1,379
Joined: 26-December 12
From: Berkshire Mtns.


Have you tried using NZ() function?

CODE
nz(ExitDate, Date())

If exit date is null it will use date.
Go to the top of the page
 
woopow
post Feb 24 2020, 02:35 PM
Post#3



Posts: 10
Joined: 27-June 03
From: Sleeping in the desert


Nope, haven't tried that yet, but will. Thanks!
Update: Perfect. It works just as I need it to do. Yay!
This post has been edited by woopow: Feb 24 2020, 02:45 PM
Go to the top of the page
 
kfield7
post Feb 24 2020, 02:37 PM
Post#4



Posts: 1,054
Joined: 12-November 03
From: Iowa Lot


Do you record when they left the program?
Go to the top of the page
 
woopow
post Feb 24 2020, 02:48 PM
Post#5



Posts: 10
Joined: 27-June 03
From: Sleeping in the desert


Yes, the exact day of the exit is stored, so moke123's solution works perfectly.
Go to the top of the page
 
kfield7
post Feb 24 2020, 03:06 PM
Post#6



Posts: 1,054
Joined: 12-November 03
From: Iowa Lot


Naturally I skimmed right past Moke's post. Glad you have resolution.
Go to the top of the page
 
moke123
post Feb 25 2020, 06:27 AM
Post#7



Posts: 1,379
Joined: 26-December 12
From: Berkshire Mtns.


yw.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    31st March 2020 - 08:41 AM