OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calculating Age Control on Form from Birthdate Control

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Sun Oct 30, 2005 1:18 pm    Post subject: Calculating Age Control on Form from Birthdate Control Reply with quote

Hi guys and gals...

The above pretty much says it. Have been looking all over for this answer but no luck so far. How do i get a control which i have placed on a form to show or calculate the age of the patient using another control on the same form which has the patient's birtdate? Am only familiar pretty much with the Wizard interface and cut and paste sort of stuff. Have been able to finally get the combo boxes up and running for the Postgresql backend. Have a nice afternoon! Very Happy OH! And not so BTW!! A BIG thanks to all the programmers who have worked so hard to come up with Version 2.0!!!
_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Oct 30, 2005 4:36 pm    Post subject: Using a calculated firld in a form Reply with quote

Alright - I am going to assume that the AGE field is a calculated field

only.

So - I will create a patients table as

patients (PatientID IDENTITY, PatNum VARCHAR(50), LastName VARCHAR(50),

FirstName VARCHAR(50), DOB DATE )

I will add some test data:

0,100-1,Smith,Thomas,1954-10-15
1,100-2,Carlisle,Rebecca,1963-01-22
2,100-3,Carlisle,Timothy,2005-08-15
3,100-4,Smith,Madeline,2005-10-24

Now the easiest way to get the AGE field is with a query adding the

calculated field.

To do this I will use the HSQLDB built-in function:

Quote:

DATEDIFF(string, datetime1, datetime2)[1]

returns the count of units of time elapsed from datetime1 to

datetime2. The string indicates the unit of time and can have the

following values 'ms'='millisecond',

'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' =

'year'. Both the long and short form of the strings can be used.



select *, datediff( 'yy', "DOB", CURRENT_DATE ) as "Age" from "patients"



Ok, but we have at least one problem and maybe two -
First because I used a calculated column the result set is read only.

This is fine for reporting but won't help me when it comes to creating

the form for data input.

The second, possible problem, is one from personal experience - is 0 an

acceptable age for the two young patients?

Let's assume it is not. HSQLDB has another command that can help us here,

the case when command.

Quote:

CASE WHEN...[1]

CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END

when expr1 is true return v1 [optionally repeated for more cases]

[otherwise v4 or null if there is no ELSE]


I will also use the CAST AS.

I will change the query to this then:



Alright, that accounts for the very young patients.

Now what about the form. I still need to have an updateble recordset, and

have the calculated field. Well, this may not seem obvious at first but

the answer is in using a sub form.

Before I create the form though I will need to turn my query into a VIEW,

and all I really need besides the Age calculated column is our PatientID.

So One last change to the select statement:

Code:

select "PatientID", CASE WHEN datediff( 'yy', "DOB", CURRENT_DATE ) <> 0

THEN CAST(datediff( 'yy', "DOB", CURRENT_DATE ) AS VARCHAR) || ' Yrs' 

WHEN datediff( 'mm', "DOB", CURRENT_DATE ) <> 0 THEN CAST(datediff( 'mm',

"DOB", CURRENT_DATE ) AS VARCHAR) || ' Mos' else CAST(datediff( 'dd',

"DOB", CURRENT_DATE ) AS VARCHAR) || ' Dys' END as "Age" from "patients"


Save the query and select the context menu (right mouse click) 'Create as View'. I named it vPatientAge.

Now create a form in design view.

Add a dataform object using the navigator.

In the dataforms properties browser set it to read the patients table.

Add the fields for PatNum, LastName, FirstName and DOB

Now bring up the dataform navigator again and selecting the form object add a new sub form.

In the property editor for this new sub form set the data source as the VIEW vPatientAge.

Set the master and detail link for the two forms to PatientID on each.

Add a text control and bind it to the column Age. Set it as read only.

When the form is finished the Form navigator should have this structure:



The form itself should is this, opened on the first record



Now the only problem left is that when you insert a new patient record using the form, the Age field will not be updated until you step off and then back onto the new record, or you move the cursor into the Age field and refresh the dataform.

You could fix this with a macro that would be called directly after the new record being posted. All the macro would need to do is a reload on the sub form.

Anyway you can grab the actual database I made for this example at

http://www.paintedfrogceramics.com/OpenOffice/MedRecords.odb


Drew Jensen
Back to top
View user's profile Send private message Send e-mail Visit poster's website
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Sun Oct 30, 2005 6:49 pm    Post subject: Calculating Age Control on Form from Birthdate Control Reply with quote

Wow, Drew, that was quite a dissertation! Thanks a geat deal! Hope you have a wonderful evening! Very Happy i'm almost afraid to say this but i was just looking for a control on a form which calculated the age, in years and months, from the other control, the DOB, which is connected to the patient table. The age control is just for temporary viewing on the form and will not be stored in the db. i'm going to see if i can use the info you so wonderfully made available to do this. i seem to remember some of the from M$Access.
_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Oct 30, 2005 7:21 pm    Post subject: Reply with quote

*chuckling*...I was thinking you would say that the whole time I was putting that together...

No problem.

I have a thread on the forum from about a month ago that shows how to calculate based on the contents on the form.

You will need to create a basic macro, call it from the 'Text modigfied' event on the data control.

Hopefully this will help, sorry if it is too simple.

In the macro be sure to get the date from the BoundField of the DOB control.

Look at this litle macro and substitute your DOB date for the variable D

Code:

Sub Main
   dim d
   d = dateSerial( 1955, 10, 31 )
   print "Years = " & year(now) - year( d )
   print "Months = " & month(now) - month( d )
End Sub
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Oct 30, 2005 7:22 pm    Post subject: Reply with quote

*chuckling*...I was thinking you would say that the whole time I was putting that together...

No problem.

I have a thread on the forum from about a month ago that shows how to calculate based on the contents on the form.

You will need to create a basic macro, call it from the 'Text modigfied' event on the data control.

Hopefully this will help, sorry if it is too simple.

In the macro be sure to get the date from the BoundField of the DOB control.

Look at this litle macro and substitute your DOB date for the variable D

Code:

Sub Main
   dim d
   d = dateSerial( 1955, 10, 31 )
   print "Years = " & year(now) - year( d )
   print "Months = " & month(now) - month( d )
End Sub


The other thread I was thinking of is
http://www.oooforum.org/forum/viewtopic.phtml?p=90581&highlight=calculated+field#90581
Back to top
View user's profile Send private message Send e-mail Visit poster's website
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Mon Oct 31, 2005 7:26 pm    Post subject: Calculating Age Control on Form from Birthdate Control Reply with quote

No problem, Drew. i`m sure your excellent explanation will be helpful to someone. Thanks a lot for the link to the previous thread. i don`t want to ask this, but am i going to have much problem "creating"a macro...??? Laughing i`ve got another question, but it`s going to have to go on another thread. Thanks again. A good deed is never lost. Have a wonderful evening! Very Happy
_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Sat Sep 08, 2007 2:12 pm    Post subject: Reply with quote

Guess what! i'm back! Finally got the db set up so that i can input into a one-to-many relationship through a form/subform setup. Phew!!!

Now, i've placed a Control on the main form to show the Age in months and years. The form also has the DOB Control (called fecha_nacimiento). On the Age control, in Events, i have this...

vnd.sun.star.script:Standard.Module1.CalcDate?language=Basic&location=application

CalcDate comes from changing the word Main in your Code to CalcDate. This way, it shows up in the Macros list as... CalcDate and i can select it.

This is what i used to create the Macro, CalcDate...

Quote:
Sub Main
dim d
d = dateSerial( 1955, 10, 31 )
print "Years = " & year(now) - year( d )
print "Months = " & month(now) - month( d )
End Sub


Quote:
Look at this litle macro and substitute your DOB date for the variable D
-- i frankly didn't understand this line, so to experiment i substituted fecha_nacimiento for d (i didn't see any variable called capital D so i supposed we were talking about the small d.).

Well, at least it didn't corrupt my form or database, but when i run the form, nothing appears in the Age box (Control).

Have i messed it up completely or will i just have to wait another couple of years until i get enough neurons together to figure it out??? Very Happy

Have a great afternoon! Smile
Quote:

_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Tue Sep 11, 2007 3:50 pm    Post subject: Reply with quote

O.k. Here goes another try...

i have inserted this Macro into the TextModified event on the Data control...

Code:
Sub Main
   dim fecha_nacimiento
   fecha_nacimiento = dateSerial(1955, 10, 31)
   print "Years = " & year(now) - year(fecha_nacimiento)
REM   print "Months = " & month(now) - month(d)
End Sub


but get no result on my Form, even after changing the DOB (fecha_nacimiento), or moving from one record to another (also after changing DOB).

Any ideas?

Have a great evening! Smile
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Thu Nov 20, 2008 11:15 pm    Post subject: Reply with quote

hi

i found this in my search for calculating age
came across based on the suggestion by sliderule to use CASEWHEN so as to check on the age validity

come to think of it
yes i would need to have such check in order to get the correct or more accurate answer

so i tried followed the instruction as shown.

Quote
code
select "PatientID", CASE WHEN datediff( 'yy', "DOB", CURRENT_DATE ) <> 0

THEN CAST(datediff( 'yy', "DOB", CURRENT_DATE ) AS VARCHAR) || ' Yrs'

WHEN datediff( 'mm', "DOB", CURRENT_DATE ) <> 0 THEN CAST(datediff( 'mm',

"DOB", CURRENT_DATE ) AS VARCHAR) || ' Mos' else CAST(datediff( 'dd',

"DOB", CURRENT_DATE ) AS VARCHAR) || ' Dys' END as "Age" from "patients"


Unquote

i get syntax error.
so what i did next, i downloaded the file sample.

it works at first.
then when i tried to modify a little bit, by adding the name etc, it did not work and syntax error message appear again.

can someone help me on this?
i'm totally new to oobase, sql etc

thanks in advance.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Nov 20, 2008 11:20 pm    Post subject: Reply with quote

Can you show what modifications you actually made?
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Fri Nov 21, 2008 12:06 am    Post subject: Reply with quote

hi drew,

thank you for the quick response.


code

SELECT "PatientID" AS "PatientID", "LastName" AS "Last Name", "FirstName" AS "First Name", "DOB" AS "Date of Birth", CASE WHEN datediff( 'yy', "DOB", CURRENT_DATE ) <> 0 THEN CAST(datediff( 'yy', "DOB", CURRENT_DATE ) AS VARCHAR) || ' Yrs' WHEN datediff( 'mm', "DOB", CURRENT_DATE ) <> 0 THEN CAST(datediff( 'mm', "DOB", CURRENT_DATE ) AS VARCHAR) || ' Mths' else CAST(datediff( 'dd', "DOB", CURRENT_DATE ) AS VARCHAR) || ' Days' END as "Age" from
"patients"


i must have unclick the [run SQL command directly]
once i press on the SQL icon, it generate the output as requested.
no more showing syntax error.
so i need to run SQL command directly for this to work.

however when i run d form [Patient], i found when i click the right button at the bottom the rest of the data would change accordingly except for the age information.
based on todays date, it shows 54 yrs for all "patients".
doesn't seem right tho.
did i do something worng again?

for the age output
is there a way to generate the output as for example 10yrs 10mths instead of the age as just 10yrs only?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group