Macros and Programming

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

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

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.

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

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

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

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

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