Normal Topic Date Calculation (Read 1027 times)
bstone30
Member
*
Offline


No personal text

Posts: 28
Location: USA
Joined: Dec 16th, 2003
Date Calculation
Oct 2nd, 2006 at 5:34pm
Print Post Print Post  
I need help with a rounding issue with a field that calculates an age.  The code I am using is Age=(@ROUND(datefield-dobfield,2))/365   This field is supposed to indicate the age of traffic offender because offenders under the age of 18 are handled differently by the court.  The datefield is the date the ticket was issued, the dobfield is the offender's date of birth.  When the offender is within a few days or weeks of turning eighteen, it rounds up to 18.  I tried changing the decimal place to three, but that, for some weird reason, is rounding to 3 decimal places above 18, not below (when the offender is within a few days of his/her 18th birthday).  The age field is formatted as a number field.   How do I refine this statement to correct for this error?
  
Back to top
 
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Date Calculation
Reply #1 - Oct 2nd, 2006 at 7:06pm
Print Post Print Post  
I haven't had a chance to test your formula yet, but you may want to change the 365 to 365.25 also.  Over 18 years you are losing 4-5 days.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
bstone30
Member
*
Offline


No personal text

Posts: 28
Location: USA
Joined: Dec 16th, 2003
Re: Date Calculation
Reply #2 - Oct 7th, 2006 at 1:13am
Print Post Print Post  
If anyone is interested, the Age field had to be formatted to 3 decimal places and the expresssion Age=(@ROUND(datefield-dobfield,2))  had to be divided by 365.22 instead of 365 to make this field calculate accurately. 

Age=(@ROUND(datefield-dobfield,2))/365.22
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Date Calculation
Reply #3 - Oct 7th, 2006 at 1:27am
Print Post Print Post  
You might want to try this one instead (courtesy of Robert Scott). It doesn't depend on estimated adjustments for leap years and such. It assumes an element on your form named DOB.

Code
Select All
Age = @year(@date) - @year(DOB) - ((@month(@date) < @month(DOB))
 or (@month(@date) = @month(DOB) and @dom(@date) < @dom(DOB)))  

  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
bstone30
Member
*
Offline


No personal text

Posts: 28
Location: USA
Joined: Dec 16th, 2003
Re: Date Calculation
Reply #4 - Oct 7th, 2006 at 2:26am
Print Post Print Post  
I wish I had known that before I spent all the time on my pathetic answer to the problem!  Ericka you amaze me.
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Date Calculation
Reply #5 - Oct 7th, 2006 at 2:32am
Print Post Print Post  
bstone30 wrote on Oct 7th, 2006 at 2:26am:
I wish I had known that before I spent all the time on my pathetic answer to the problem!  Ericka you amaze me.


I can't take credit for this one. It's been floating around for awhile in various forms. I copied it for you off an older thread in our Programming Examples Board. Robert Scott dug it up and posted it there.
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged