if formula in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report which is based on years of students in the college. There
will always only be 2 years in the report because its a 2 year college. The
lower class is called A1 and the 2nd class is called A2.

Now it is grouped by student year (ie. in which year they began the
college). So for e.g it says 2003 at the top and then a list of students.
Then it says 2004 and a whole list of students. But the report is based on a
parameter query so the user can get any 2 year period that he wants.

so what i want the if formula to say is look at student year, for the number
thats higher i.e. 2004 say A1 and the lower number say A2

can this be done?!!
 
so in the report, you have the records grouped by the year field? and the
group is sorted Ascending - meaning the first year students first, and then
the second year students?

if the above is correct, try this:
in report design view, in the "year" group Header section, add an unbound
textbox. i'll call it txtHeaderCount. open the Properties box and set the
following three properties, as

ControlSource: =1
Format: \A@
RunningSum: Over All

note that if you have the "year" group sorted Descending - meaning the
second year students first, and then the first year students - the above
solution won't work for you. in that case, do the following instead, as

in report design view, in the "year" group Header section, add an unbound
textbox. i'll call it txtHeaderCount. open the Properties box and set the
following three properties, as

ControlSource: =1
Visible: No
RunningSum: Over All

add another unbound textbox to the "year" group Header section, i'll call it
txtClassNumber. in the section's OnPrint event procedure, add the following
VBA code, as

If Me!txtHeaderCount = 1 Then
Me!txtClassNumber = "A2"
Else
Me!txtClassNumber = "A1"
End If

if you don't know how to create an event procedure, "Create a VBA event
procedure" at http://home.att.net/~california.db/downloads.html for
illustrated instructions.

hth
 
Back
Top