| View previous topic :: View next topic |
| Author |
Message |
frazelle09
OOo Enthusiast
Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California
|
 Posted: Sun Oct 30, 2005 1:18 pm Post subject: Calculating Age Control on Form from Birthdate Control |
 |
|
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!  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 |
|
 |
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Oct 30, 2005 4:36 pm Post subject: Using a calculated firld in a form |
|
|
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 |
|
|
frazelle09
OOo Enthusiast
Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California
|
| Posted: Sun Oct 30, 2005 6:49 pm Post subject: Calculating Age Control on Form from Birthdate Control |
|
|
Wow, Drew, that was quite a dissertation! Thanks a geat deal! Hope you have a wonderful evening! 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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Oct 30, 2005 7:21 pm Post subject: |
|
|
*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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Oct 30, 2005 7:22 pm Post subject: |
|
|
*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 |
|
|
frazelle09
OOo Enthusiast
Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California
|
| Posted: Mon Oct 31, 2005 7:26 pm Post subject: Calculating Age Control on Form from Birthdate Control |
|
|
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...???  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!
_________________
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 |
|
|
frazelle09
OOo Enthusiast
Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California
|
| Posted: Sat Sep 08, 2007 2:12 pm Post subject: |
|
|
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???
Have a great afternoon! 
_________________
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 |
|
|
frazelle09
OOo Enthusiast
Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California
|
| Posted: Tue Sep 11, 2007 3:50 pm Post subject: |
|
|
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! |
|
| Back to top |
|
|
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
| Posted: Thu Nov 20, 2008 11:15 pm Post subject: |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Thu Nov 20, 2008 11:20 pm Post subject: |
|
|
| Can you show what modifications you actually made? |
|
| Back to top |
|
|
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
| Posted: Fri Nov 21, 2008 12:06 am Post subject: |
|
|
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 |
|
|
|
|
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
|
