Help with Iif on Report

[hanabananaiguana]

I can't seem to see what's wrong with my logic, although I am sure it's quite simple:

I have a report showing students, with their year of attendance under each term, 1-4. Some of them attended the Winter term, some Spring, and some both. I need an unduplicated count of students, regardless of year.

I had been using this:
=Sum(IIf([2012 w]>=1,1,IIf([2013 s]>=1,1,0)))

In other words, if this student attended this particular winter term, count him as 1, otherwise, if he attended Spring, count him as 1, otherwise, 0.

After counting hundreds of students manually, I have verified that the results are not correct, but I just can't figure out why. Any suggestions would be greatly appreciated.

 

[Access Developer]

First, I'm a little confused by "I have a report showing students, with
their year of attendance under each term, 1-4. Some of them attended the
Winter term, some Spring, and some both. I need an unduplicated count of
students, regardless of year." As an example of what I'm confused about:
"with their year of attendance under each term". How many years does the
data cover? For example, if it includes all students from 1980 through 1997,
you'd use a different approach if its only for Winter term of 2012 and
Spring term of 2013.

Where/when is your code executing? FYI, the Format and Print events can
fire more than once for each line and it's not simple to predict how many
times they will fire. That's why it's not a good idea to do accumulations
in report code

Why are you counting hundreds of students by hand? That is, why are you not
testing with a subset of the data until you resolve all issues you can, and
only testing with a full set of data as a final test?

What entity does a line of the report represent -- a single student, or a
student in a term, or something else?

 

[hanabananaiguana]

First, I'm a little confused by "I have a report showing students, with

their year of attendance under each term, 1-4. Some of them attended the

Winter term, some Spring, and some both. I need an unduplicated count of

students, regardless of year." As an example of what I'm confused about:

"with their year of attendance under each term". How many years does the

data cover? For example, if it includes all students from 1980 through 1997,

you'd use a different approach if its only for Winter term of 2012 and

Spring term of 2013.



Where/when is your code executing? FYI, the Format and Print events can

fire more than once for each line and it's not simple to predict how many

times they will fire. That's why it's not a good idea to do accumulations

in report code



Why are you counting hundreds of students by hand? That is, why are you not

testing with a subset of the data until you resolve all issues you can, and

only testing with a full set of data as a final test?



What entity does a line of the report represent -- a single student, or a

student in a term, or something else?



--

Larry Linson

Microsoft Office Access MVP

Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

I can't seem to see what's wrong with my logic, although I am sure it's

quite simple:

I have a report showing students, with their year of attendance under each

term, 1-4. Some of them attended the Winter term, some Spring, and some

both. I need an unduplicated count of students, regardless of year.

I had been using this:

=Sum(IIf([2012 w]>=1,1,IIf([2013 s]>=1,1,0)))
In other words, if this student attended this particular winter term,

count him as 1, otherwise, if he attended Spring, count him as 1,

otherwise, 0.

After counting hundreds of students manually, I have verified that the

results are not correct, but I just can't figure out why. Any suggestions

would be greatly appreciated.

Explained in more detail in comp.databases.ms-access. Should I repeat here or meet you over there?

 

[hanabananaiguana]

Why are you counting hundreds of students by hand? That is, why are you not
testing with a subset of the data until you resolve all issues you can, and
only testing with a full set of data as a final test?
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

This was a big help. I did work on a small subset and also got rid of a little extra complexity. I do think it's working now, based on about 40 students. I am not sure if I should count the 100 group again, though...

Where/when is your code executing? FYI, the Format and Print events can
fire more than once for each line and it's not simple to predict how many
times they will fire. That's why it's not a good idea to do accumulations
in report code

My code is just a field in the Report Header, which, I hope, is doing a sum on the cross tab query. I know Access sometimes gives funny results in a report, but if it seems to be working now, can I rely on it?

 

[Access Developer]

The only thing you have to lose by checking the results with all the data is
your time and effort; on the other hand, you'll have gained a lot if you
find another 'last-known-error' that didn't show up in the subset, rather
than having the _user_ discover it.

Since your code is actually an expression that is Control Source for a
Control, you don't have the problem I mentioned; it only occurs in event
code.

I hadn't noticed that you'd posted essentially the same question in CDMA.
That's one of the problems with multi-posting; if you think you need the
exposure of two newsgroups, cross-posting is better so both the question and
answers will show up in both newsgroups.

In any case, glad you got it working... that's the main point.