Date Calculations p9 1

[Guest_imported]

I use a p9 database to track client info such as date of birth, appointment date, etc. I use p9 interactively with a form to make input easier. I know how to create forms and, for the most part, manipuate stuff to get what I want. However, I can't seem to figure out how to subtract the date in one field from the date in the other field, and put the result into another field on the form. Is there a way to do that so I can get a client's age to display on each form? Example:

Appointment date - Date of birth = age of client


????


Would I have to create another field in the database to hold the result of that calculation before I plug it into the form?

 

[Lewy]

Daniel,

If it is a simple 'age' that you wish displayed on the form, then place a calculated field on the form and apply the following calculation:

int(longint(today()-[cust1.DoB])/365.25)

Ensure also that ALL dates are enterred as 4 digit year format otherwise you may get some strange results! I was nearly -42 years old......

What sort of application are you trying to build? I have recently completed the core of my database for an Optician's practice.....this took in the region of 6 months part time work, if I canbe of further help, please do not hesitate to contact me.

Regards,

Lewy

 

[DLDavis]

I plugged in your formula..and got some errors at first, but was eventually able to figure out where to plug in the field name...and it worked. Thanks a million. As an interactive user, it would have taken me awhile to figure out how to do that...and I NEVER would have known the /365.25 thing.

Is there a way to get the output into date/month, so that it would return 8-2 for a child aged 8 years 2 months? Also, in using the TODAY value...does that default to "today" meaning the last time the record was changed, or does it update each record according to the date on the machine? I assume the former.

 

[Langley]

today() returns the current date, assuming the workstation date/time settings are correct. Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[Lewy]

Daniel,

I am sure it is possible to seperate years and months, I haven't yet worked it out. I will try and look at it in the next couple of days, but at a quick guess it will involve an Int and Mod function and a possible conversion to string value.

Will be in touch soon.

Regards,

Lewy

 

[Lewy]

Daniel,

Looking at the problem in it's simplest approach, I would place another calculated field on the form to give the months part of the age and use the following formula:

((longint(today()-[cust1.DoB])/365.25)-(int(longint(today()-[cust1.DoB])/365.25)))*12

Then in the properties of the field set the format to number and then integer, this will then display the correct information. If you need to output the information into a report, then a simple modification to the two formulae should work.

By the way, what sort of application are you building?

Regards,

Lewy

PS don't forget to replace Cust1.DoB with your own table & .field name

 

[DLDavis]

Wow! I had dropped by the wayside..hadn't looked at this post in awhile, figuring I'd gotten all the replies I was going to get. Thanks a lot for the months calc formula.

the type database is just (right now) a flat file client tracking database...to keep up wiht when they were seen, and the relevant info.