Home

Macros and Programming

 

CardboxForumsMacros and Programming > "Calculating Age"

Calculating Age

Does anyone have a macro written that will either calculate an age on 1 record or all records.

Current user: [none]
Register / Log In · Help

Posted By Post

TF

23-Mar-2006 14:47

Grateful for any help. Even a pointer to a specific help page would be great.

TF

23-Mar-2006 16:31

I should add that I have been trying the following

EditRecord
GoToField AGE
TypeText DateDiff("YYYY",DOB,Date)

DOB being a field for date of birth.
AGE being the fieldd I want to display age in years, rounded down, expressed as years only.
Date as far as I can see represents todays date.

Datediff should calc diff in years (YYYY) between todays date and DOB field.

It doesnt...yet

Mary Doyle

23-Mar-2006 18:56

Hope this helps. Mary

a)

EditRecord
DOB = Fields("dob")
Age = DateDiff("yyyy",DOB,Date)
Fields("AGE") = Age
SaveRecord

b)

If you want to process a set of records use a loop (see Cardbox manual Macros and Programming)

n = RecordCount

For i = 1 to n

EditRecord
DOB = Fields("dob")
Age = DateDiff("yyyy",DOB,Date)
Fields("AGE") = Age
SaveRecord

NextRecord
Next

Don't forget to select the records you want to process first. You could maybe add that in to the macro.

Charles Welling

24-Mar-2006 13:30

Please note that DateDiff is notoriously incorrect. Here's a small macro that will do the trick as well without using DateDiff. It assumes that DOB is in the format yyyy.mm.dd, but it can be adapted as necessary. It simply takes the DOB and takes it apart. It looks at the month to see if you already had your birthday. If the months are the same, it looks at the day to see if you already had your birthday. If it is your birthday, a "happy birthday" will be added (just for the sake of demonstration).

Charles

DOB=FIELDS("DOB")
Year_Of_Birth=NumberFromCardbox(LEFT(DOB,4))
Month_Of_Birth=NumberFromCardbox(MID(DOB,6,2))
Day_Of_Birth=NumberFromCardbox(RIGHT(DOB,2))

Dnow = Now

if Month_of_Birth > Month(Dnow) then
Age=Year(Dnow)-Year_of_Birth
Age=Age-1
end if

if Month_of_Birth < Month(Dnow) then
Age=Year(Dnow)-Year_of_Birth
end if

if Month_of_Birth = Month(Dnow) AND Day_of_Birth < Day(Now) then
Age=Year(Dnow)-Year_of_Birth
end if

if Month_of_Birth = Month(Dnow) AND Day_of_Birth > Day(Now) then
Age=Year(Dnow)-Year_of_Birth
Age=Age-1
end if

if Month_of_Birth = Month(Dnow) AND Day_of_Birth = Day(Now) then
Age=Year(Dnow)-Year_of_Birth & "! Happy Birthday"
end if

FIELDS("AGE")=Age

Helen

22-Feb-2011 11:42

Hi Charles, I can't got this macro to work consistently. Our date format is dd.mm.yyyy, so I have amended the first section as follows:

DOB=FIELDS("DOB")
Year_Of_Birth=NumberFromCardbox(LEFT(DOB,4))
Month_Of_Birth=NumberFromCardbox(MID(DOB,6,2))
Day_Of_Birth=NumberFromCardbox(RIGHT(DOB,2))

the rest is the same. However, when there are two digits in the month i get the following message:

type mismatch (position in the macro: line 3, character 1)

However it works fine when there is only one digit in the month.

Would be grateful for your help.

Helen

Charles Welling

22-Feb-2011 13:07

Helen, you say that you've amended the first section, but I can't see any difference with the original lines. If a date would be "22.02.2011" then the above lines would separate the date into:

22.0
20
11

However:

Year_Of_Birth=NumberFromCardbox(RIGHT(DOB,4))
Month_Of_Birth=NumberFromCardbox(MID(DOB,4,2))
Day_Of_Birth=NumberFromCardbox(LEFT(DOB,2))

should do the trick. By the way: the months and days should always leading zeros. So, June would be "06", not "6".

Charles

bert

22-Feb-2011 13:47

With this you do not get problems with leading zero's. Only two dots are needed to separate the parts.

DOB=FIELDS("DOB")
arr = split(dob,".")
Day_Of_Birth=arr(0)
Month_Of_Birth=arr(1)
Year_Of_Birth=arr(2)

regards
bert

Mary Doyle

22-Feb-2011 23:00

Hi Charles,

RE: By the way: the months and days should always leading zeros. So, June would be "06", not "6".

Do you mean by this that in Helen's database the months and days should always have leading zeros for consistency? We don't use leading zeros because it speeds input, but all our date fields have validation to ensure consistency?

Just wondering.

Mary

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home