Would DOB happen to be one of the fields you have on your parent table? Could you just add 22 years to that field?
The DOB field is on the child table with a portal on the parent table.
This may seem like a "duh" question, but I don't know all the details of your dbase's function...
Could there ever be more than one child record per parent record?
If not, what is the purpose of the child table?
If so, how do you distinguish which child record the 22yrs should be based upon? We can then use that distinction to get your calc done.
In other words I want to calculate the future date of when the child turns 22 yrs of age based on the end of eligibility date on the parents table.
As I reread your OP, this seems to be the crux of it. How is the child turning 22yrs old based on the end of eligibility? Could you describe more about what exactly the date should be and how you would calculate it if you were doing this on paper?
Yes, there can be one or up to nine children to a parent record. Each child is identified by a child id serial number. And entered through a portal in the parent table. Each parent table also has a unique serial number.
In our database the child is only eligible until they are 22yrs for our services and that is also based on the End of Eligilbility date which only is for three years.
Getting confused between children of humans and children of records...
So a person is eligible until they are 22yrs or have been with you for three years whichever comes first?
The start of service date (SOS) is on the parent table, and the person's DOB is on a child record...which of the 9? All?
The calc resulting in date is starting to look like:
If ( (Parent::SOS + 1095) < (ChildTable:: DOB + 8036) ; Parent::SOS + 1095 ; Month(ChildTable:: DOB) & "/" & Day(ChildTable:: DOB) & "/" (Year(ChildTable:: DOB) + 22) )
and it raises the question of accuracy needed. Is a year 365 days, or must leap years be accounted for? the 8036 includes 6 extra days for leap years across 22 years...you could add the month/day/year parsing into the IF condition to make this more accurate.
Edited to remove emoticons.
Thank you Mr. Ninja, you are the best... it worked out perfectly.. :smileywink:
One more question How do I add a field to an existing portal?
First, the field must exist in the table the portal show records from (or a related table to it). If it is New-New...add it to the appropriate table under File>Manage>Database.
Then simply go into Layout mode and put the field into the portal line using the field tool.
Have you put a new field onto a layout before? If so, a portal is no different, you just need to stay within the lines...
thanks Ninja.... for all your help