Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

@Age Formula (Ken Hamady) Suppress Formula help needed!

Status
Not open for further replies.
Tech2377

Tech2377

Programmer
Nov 13, 2007
81
I am using Ken Hamady's Age Formula (
Code: 
WhileReadingRecords;
DateVar Birth:= date({SB1001_main.PatientBirthdate});   // Replace this with your field for Date Of Birth
DateVar Ann := date({SB1001_main.ApptStart});  // Replace this with CurrentDate to get their age as of the time of the report
                                                            //or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)  
then Year (Ann) - Year(Birth)  
else Year (Ann) - Year(Birth) -1

I need some help on a report. My report currently has 5 detail bands (A - E) and each utilize the @Age formula. Its seems the weeks and months at certain levels trigger the wrong form. I just came across an 8 week old and a 7 month old that were incorrect and printing the 7-18 yr forms. I am a bit stumped as to where the defining problem is. Perhaps I should rework this entirely to pull the right form. Any questions, please ask. I appreciate any help!

Birth to 6 yrs Old. (Details A)
Code: 
ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

Female 7 - 18 yrs old (Details B)
Code: 
ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

Male 7 - 18 yrs old (Details C)
Code: 
ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

Adult Female (over 18 yrs old) (Details D)
Code: 
ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

Adult Male (over 18 yrs old) (Details E)
Code: 
ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR 

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
 
Sort by date Sort by votes
You don't need tonumber() as it is already a number, and you are overcomplicating this. Try the following suppression formulas:

det_a:
{@Age} > 6

det_b:
{SB1001_main.PatientSex} = 'M' or
{@Age} < 7 or
{@Age} > 18

det_c:
{SB1001_main.PatientSex} = 'F' or
{@Age} < 7 or
{@Age} > 18

det_d:
{SB1001_main.PatientSex} = 'M' or
{@Age} < 19

det_e:
{SB1001_main.PatientSex} = 'F' or
{@Age} < 19

The reason you were getting in trouble with your formulas was because you weren't setting off your "or" statements with parens to clarify what "and/or" clauses "go together."

-LB
 
Upvote 0 Downvote
LBass:

I set up a male patient with a DOB = 11/01/2007. This made his age = 8 weeks. For some reason, it pulled the Male 7-18 yr old detail band (details C). It should have pulled Details A tab. Any thoughts?
 
Upvote 0 Downvote
Ken's formula doesn't look at weeks or months of age, so how did you arrive at {@age} = 8 weeks? This is not a problem with the suppression formula, but with your age formula.

-LB
 
Upvote 0 Downvote
Wow ... I guess if his formula isnt pulling Weeks or months, then using his formula wont help me too much. I am calculating Age in the Stored Procedure (SQL). Please see the below section (specific to Age). Do you think I could use this and have the suppress statements altered?

Code: 
Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = @AppointmentsId

SELECT  '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''), 
        '291.PatLast'=IsNull(pp.Last,''),
        '292.PatFirst'=IsNull(pp.First,''),
        '293.PatMiddle'=IsNull(pp.Middle,''),
        '294.PatientAddr1'=IsNull(pp.Address1,''),
        '295.PatientAddr2'=IsNull(pp.Address2,''),
        '296.PatientCity'=IsNull(pp.City,''),
        '297.PatientState'=IsNull(pp.State,''),
        '298.PatientZip'=IsNull(pp.Zip,''),
		'299.PatientCountry' = ISNULL(pp.Country,''),
        '300.PatientBirthdate' = pp.Birthdate,
        '301.PatientSex'=IsNull(pp.Sex,''), 
        '302.PatientPhone1'=IsNull(pp.Phone1,''),
        '303.PatientSSN'=IsNull(pp.SSN,''),
        '304.PatOccupation'=IsNull(pp.EmpOccup,''),
        '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
        '306.PatBudget'=IsNull(g.Budget,0),
        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
		'308.PatSuffix'=IsNull(pp.Suffix,''),
		'400.PatientAge' = CASE	WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
			WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
			ELSE '' END
FROM    PatientProfile pp 
		LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE	pp.PatientProfileID = @PatientProfileId
 
Upvote 0 Downvote
Please show a sample of how the age displays if you place the age field in the detail section.

-LB
 
Upvote 0 Downvote
If I displayed the age field in the detail section for my test patient (DOB 11/01/2007) it would display as: "8 wks
 
Upvote 0 Downvote
I mean for you to show a sample of multiple results spanning the range of ages.

-LB
 
Upvote 0 Downvote
Actually, I think you could convert the age like this:

//{@AgeinYrs}:
if right({age},3) <> 'yrs' then
0 else
truncate(val({age}))

Then use {@AgeinYrs} in my earlier suppression formulas instead of {@Age}.

-LB
 
Upvote 0 Downvote
LBass:

I created a new formula @AgeinYrs
Code: 
//{@AgeinYrs}:
if right({SB1001_main.PatientAge},3) <> 'yrs' then
0 else
truncate(val({SB1001_main.PatientAge}))

Then in each of the detail sections I replaced {@Age} with {AgeinYrs}. With my prior patient (my test patient), it still pulls the MALE 7-18 YR OLD detail band (Details C) and it should have pulled Birth to 6 yrs Old. (Details A).
 
Upvote 0 Downvote
Then please show how your test patient appears in {SB1001_main.PatientAge}.

-LB
 
Upvote 0 Downvote
If I displayed the age field in the detail section for my test patient (DOB 11/01/2007) it would display as: "8 wks"

I wonder if I should create something different in SQL to make this easier ... ?
 
Upvote 0 Downvote
If it did display that way, then it would show 0 for {@Age inYrs} and the suppression formula would work correctly, so perhaps you are entering your test record into the database incorrectly?

-LB
 
Upvote 0 Downvote
lbass:

I went in a re-formatted the SQL:

Code: 
Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = 1095

SELECT  '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
        '291.PatLast'=IsNull(pp.Last,''),
        '292.PatFirst'=IsNull(pp.First,''),
        '293.PatMiddle'=IsNull(pp.Middle,''),
        '294.PatientAddr1'=IsNull(pp.Address1,''),
        '295.PatientAddr2'=IsNull(pp.Address2,''),
        '296.PatientCity'=IsNull(pp.City,''),
        '297.PatientState'=IsNull(pp.State,''),
        '298.PatientZip'=IsNull(pp.Zip,''),
        '299.PatientCountry' = ISNULL(pp.Country,''),
        '300.PatientBirthdate' = pp.Birthdate,
        '301.PatientSex'=IsNull(pp.Sex,''),
        '302.PatientPhone1'=IsNull(pp.Phone1,''),
        '303.PatientSSN'=IsNull(pp.SSN,''),
        '304.PatOccupation'=IsNull(pp.EmpOccup,''),
        '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
        '306.PatBudget'=IsNull(g.Budget,0),
        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
        '308.PatSuffix'=IsNull(pp.Suffix,''),
        '400.PatientAge' = CASE    
			WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
            WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
            ELSE '' END,
        CASE When Year(@ApptDate - pp.birthdate - 1) % 100 < 7 Then 'A'
             When Year(@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = 'F' Then 'B'
             When Year(@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = 'M' Then 'C'
             When pp.Sex = 'F' Then 'D'
             When pp.Sex = 'M' Then 'E'
             Else 'Unknown' End As [401.PatientAgeGroup]
             
FROM    PatientProfile pp
        LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE    pp.PatientProfileID = 319

So now I am getting a "A", "B", "C", "D" or "E" based on the prior setup. Can you advise me on the suppress statements?
 
Upvote 0 Downvote
This seems self-explanatory.

det_a:
{PatientAgeGroup} <> "A"

det_b:
{PatientAgeGroup} <> "B"

etc.

-LB
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Locked
  • Question
Date comparison Formula
Replies
2
Views
316
TPG_IT
T
Corinne30
  • Locked
  • Question
Division of currency formula with/without rounding
Replies
7
Views
521
dgillz
dgillz
john432
  • Locked
  • Question
OLE Objects in your formula 1
Replies
2
Views
248
john432
john432
Tichaona
  • Locked
  • Question
Row numbering formula not working
Replies
4
Views
235
lbass
lbass
john432
  • Locked
  • Question
Crystal Report formula (finding with key words) 1
Replies
2
Views
244
john432
john432

Part and Inventory Search

Sponsor

Back
Top