Calculations within a report

[henp]

I'm a new access user who needs help with reports. I've set up a table containing data and a form to update. I'm trying to create a report with a box that would, using expressions, count the number of minus numbers and the number of + numbers (all in one column)and show them as a percentage. I could not find it in help and am so new would like the exact formula for the expression.

Thanks so much for your help,

Kirsten.

 

[dhookom]

Could you type a sample of your data and your desired outcome/display on your report?

Duane
MS Access MVP

 

[henp]

Hey Duane,

Thanks for your help on this. Sample Data would be in a single column:-

AMOUNT
($79.93)
($88.88)
($79.93)
$79.93
$29.99

The report should show the percentage of plus figures in this case 40%.

Thanks again,

Kirsten

 

[dhookom]

In the report footer, use a control source like:
=Abs(Sum([Amount]>0))/Count(*)

Duane
MS Access MVP

 

[henp]

Duane,

Thanks for your response, I'll try it and let you know.

Kirsten.

 

[uscitizen]

i hope you don't mind if i sort of extend this thread just a bit regarding calculations in a report.

i want to modify a report to include a computed/calculated quantity which represents the average annual enrollment in each study reported on.

i prepared the following expression using the expression builder:

=[PRMS -- Report]![CountOfMedRecNum]/(([Date_Accrual_Closed]-[PRMS -- Report]![DateStarted])/365.25))

what i'm trying to do is get ms a2k to compute the number of days separating the 'date started' and the 'date accrual closed' for each study. then to compute the number of 'years' (assuming crudely that there are 365.25 in a 'typical' year) and divide this into the total number of patients ('countofmedrecnum') yielding the typical count of patients enrolled in a 'typical' year.

'Date_Accrual_Closed' is a parameter prompt in this report ('PRMS -- Report') so it's expected to get asked for it. why am i getting a parameter prompt for 'PRMS -- Report' though -- assuming my logic's right?

any reporting experts out there?

 

[dhookom]

Why are you using "PRMS--Report"? Is that the name of a report or query? If these are fields in the report, then you should not need PRMS--Report.

Have you tried:
=[CountOfMedRecNum]/(([Date_Accrual_Closed]-[DateStarted])/365.25))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi,

when i built this function using the 'builder' widget thing, i guess it automatically brought along the report reference.

i did some more research since posting this and found some chatter about a DateDiff function. it's not working for me though: i am getting ridiculously large intervals using

=DateDiff("y",[DateStarted],[Date_Accrual_Closed])

i thought the "y" in there's supposed to handle the part about taking the interval from start to finish and expressing it as years, but i have the sneakingest suspicion it's reporting it as days instead. ever used this function?

 

[dhookom]

I don't think you want to use "y" but could use "yyyy". "Y" gives you the number of days. Also, DateDiff() will return the number of New Years celebrations between two dates.
DateDiff("yyyy",#1/1/2003#,#12/31/2003#) = 0
DateDiff("yyyy",#12/31/2003#,#1/1/2004#) = 1
The first represents 364 days and the second only 1.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

yes! i sort of did some more research after posting the last reply to yours and saw the reference to 'yyyy' vs 'y'.

there's a fly in the ointment though, i'm seeing '#Num!' and '#Div/0!' in some instances.

in every instance the date started was in 2003. also there's an underlying 'pattern' emerging:

a) in the '#Num!' instance, the CountofMedRec value's a zero,

b) in the '#Div/0!' instance, the CountofMedRec value's non-zero.

is there some way around this. i sort of see the problem, but given 'a', can we force a report of '0' (zero) and in the instance of 'b' can we force a report of whatever the CountofMedRec value is?

 

[dhookom]

You didn't state whether DateDiff() and its peculiarities worked for you. Since you are averaging, you might want to use:
=IIf((([Date_Accrual_Closed]-[DateStarted])/365.25)) =0,0, [CountOfMedRecNum]/(([Date_Accrual_Closed]-[DateStarted])/365.25)) )
A value of 0 in CountOfMedRec should not cause an error. There is something else wrong. Is the value ever Null? If so, try:
=IIf((([Date_Accrual_Closed]-[DateStarted])/365.25)) =0,0, Nz([CountOfMedRecNum],0)/(([Date_Accrual_Closed]-[DateStarted])/365.25)) )


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi

i apologize for leaving out the answer to your question: yup, the DatePart function finally managed to work albeit with the said quirks.

i don't beleive (put that in italics) there are any nulls, and i wanted to remark about the Iif-statements -- in the instance where you're testing for "=0", could we re-work it to say 'if the interval is under a year, ie less than 365/365.25, then let's use CountofMedRec, i.e. the number enrolled until the DateAccrualClosed (which will tend to be entered as the date the report's being generated by the user)? i know that the this wasn't the original intent when i drafted my inquiry, but on re-examination, it seems to me that it wouldn't be all that helpful to say that because a NewYear's hadn't elapsed that all those subjects entered didn't belong in the CountofMedRec column.

 

[dhookom]

You shouldn't have any issues with changing
[Date_Accrual_Closed]-[DateStarted])/365.25)) =0
to find all less than 1.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

this little story seems to have gone around the corner a bit:

can you tell me what this bit of code's supposed to do:

=IIf((([Date_Accrual_Closed]-[DateStarted])/365.25)) < 1.0,0, Nz([CountOfMedRecNum],0)/(([Date_Accrual_Closed]-[DateStarted])/365.25)) )

what i think it's going to do is evaluate the interval in terms of an 'average' calendar yr; if the interval computed is under 1.0 yrs then it will pop the value '0' (zero) into the average annual accrual rate field we're trying to compute, else (if the interval's 1 or more years) it will compute the ratio of CountofMedRecNum/(this interval) -- and when CountofMedRecNum is null, it will substitute '0' (zero) for it and therefore the ratio will be '0' zero.


i seem to be having this 'other' problem now: when i paste this code into the unbound text control i've dragged onto my report for it (into the 'Control Source' property), and run it, nothing whatsoever appears in the designated column on the report! go figure.

before i even run the report, when i close the property window of the unbound text control, ms a2k tells me 'the expression you entered has a function containing the wrong number of arguments'

 

[ammujr]

HI,
I have a question along the same line. I have a table whicb updated by a form. I am trying to create a Quality Survey. So the survey has a rating system from 1-5(one the worst 5 the best) and 5 categories to rate. I was wondering if I did a query in access, could i get the average percentage of each rating and put them in a chart. I figured the chart wouldbe report. but I couldn't get all the counts and avg on one query. DO i have to do it on seperate queries and then create the count?
thanks

 

[dhookom]

You need to count your parens. The syntax should be:
=IIf( ([Date_Accrual_Closed]-[DateStarted])/365.25 < 1.0, 0, Nz([CountOfMedRecNum],0)/([Date_Accrual_Closed]-[DateStarted])/365.25)

This expression states if the number of days divided by 365.25 < 1 then return 0. Else, return the CountOfMedRecNum divided by the number of days divided by 365.25.

This logic came from your first post on this thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

welll....

i pasted:

=IIf(([Date_Accrual_Closed]-[DateStarted])/365.25<1,0,Nz([CountOfMedRecNum],0)/([Date_Accrual_Closed]-[DateStarted])/365.25)

into the Control Source property and it's returning #ERROR in each instance.....but perhaps i think i know the reason why: 'Date_Accrual_Closed' is entered once, by the user, at the time the report's run, using an unbound text box i placed in the header field with the control source set to &quot;[To Date Accrual Closed:]&quot;. would i be correct is inferring that while the DateDiff function can deal with it, that ms a2k doesn't deal with it when using the above arithmetic. if that hypothesis is correct, then it seems the next logical step is to somehow capture this datum as a genuine date valued control. could that be done using the prompting mechanism/approach; i've tried a few permutations on my orig'l strategy but, alas, no cigar.

 

[dhookom]

ammujr,
You might want to see how this is done in At Your Survey available at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'.

Watch for word wrap or other in the url. You may need to search

http://www.rogersaccesslibrary.com/OtherLibraries.asp

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

uscitizen,
That's why one of my earlier replys stated &quot;If these are fields in the report, then you should not need PRMS--Report.&quot; Apparently Date_Accrual_Closed is not a field in the report's record source. You can make it one by using
Date_Accrual_Closed:[To Date Accrual Closed:] as a column in your query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi dhookum,

the problem was solved by using the (recently uncovered existence of the) CDate function which had the salutory effect of allowing me to use the 'Date_Accrual_Closed' value entered by the user when running the report (which it turns out is 'just' text) and convert it to a 'real date' which ms a2k uses in the computations.