Re: Conditional printing of records in the detail section of a rep

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi, Allen,

OK, let's try this again. But I'm not going to get as detailed as last
night, in case the system crashes again.

The query option, as outlined in your post, doesn't work. That seems to be
due to the fact the field, in the data entry form, I would like to check for
a number greater than zero does not exist in the database table. It exists
only in the form, where it sums the data in other fields in the table.

I called this a calculated field in the original post. The field, called
"Total Offering", sums the numbers from approximately 35 numerical fields in
the table, and displays the total in the form display.

A simplified example of the database would be: There are 3 fields in the
table, A, B, and C. In the data entry form, you may or may not enter the
data in to A, B, and C. The default data is zero. But in the form only,
there is a 4th field, D, that displays the sum of the other 3 fields, i.e. A
+ B + C. I need to see if the sum displayed in D is greater than zero. If
it is greater than zero, I want specified data from that record to print. If
the data is zero, null, anything else, the record is NOT to print.

Is that clear as mud????????? LOL

Am I going to have to build a query that first sums all the necessary table
cells, check to see if it is greater than zero, and go from there?

Is there, somewhere on MS's site, a file that lists and explains the
commands used in the Event Procedure?

I need to figure out what an unbound form is too. LOL

Ken







"Allen Browne" wrote:

There is a field (say Amount) that you want to suppress if it contains zero.
In query design, in the Criteria row under this field, enter:
Is Null OR <> 0

If you only want to suppress the zero sometimes, you could create a little
unbound form with a command button to open the report filtered, and omit the
criteria from the query. You set the command button's On Click property to:
[Event Procedure]
Then click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
Dim strWhere As String
strWhere = "(Amount Is Null) OR (Amount <> 0)"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ken Springer" <wordworks.nospam@xxxxxxxxxxxxxx> wrote in message
news:91EB1FA1-7B7C-41B4-8FFA-26FCC4813AB0@xxxxxxxxxxxxxxxx
Hi, Allen,

LOL!! You give me credit for know way more about Access than I do!

Until I read through it a half dozen times, everything was gibberish to
me.
I haven't got a clue as to how to do this with a query, but I think I
understand the idea behind the suggestion in the 2nd paragraph. But I've
no
idea how to accomplish it.

I was hoping for someone to tell me what to type!!!! (This is where I
really wish I had that set of books that were destroyed in the fire.
:-( )

That being said, maybe some of the following will be helpful for those
wishing to len a hand!!!!!

1. Using Access 97.
2. There wil never ever be more than 500 records in our collective
lifetime. It's a church offering database that keeps track of weekly
tithes
for 3 very small churches.

I'm sitting here wondering what else to add to this post that would be
helpful, but I don't know what else to add.

Might be better for folks to ask me for any information needed.

Thanks.



"Allen Browne" wrote:

Ken the best way to do this is to use a query as the source for the
report,
and put criteria in the query.

Alternatively, if you sometimes want all records and sometimes limited
records, use the WhereCondition for OpenReport.

This gives much better results than trying to suppress the record after
it
has been loaded for the report. Although it is possible to use the Format
event of the section to set runtime properties such as PrintSection,
NextRecord, and MoveLayout, doing so generates several problems. Totals
are
wrong (since the records were still present, just suppressed), layouts
are
wrong (if you print only some pages of a report and hence the events
don't
fire for all pages), and you even get weird nonsense like "Page 9 of 7.)

"Ken Springer" <wordworks.nospam@xxxxxxxxxxxxxx> wrote in message
news:18BB1732-7A74-443A-A8D7-1446C40E2D47@xxxxxxxxxxxxxxxx
It's been a long time since I did anything in Access, and it was Office
4.3
Professional for Win for Workgroups! LOL And I had access to a
GREAT
set
of books that were destroyed in a fire. :-(

I am trying to create a report that will selectively print a record's
contents based on the value in a particular field. If the field
contains
a
zero, the record does NOT print. If the value in the field is
positive,
the
record will print. There are no negative values.

The field containing the number is a calculated field in a form, not a
field
from a table.

I can get the records to print, just not conditionally. I.E., even
records
with 0 in the field print, or my use of the Expression Builder gives me
an
error message about systax or something.

Would I be correct to believe this expression is based on Visual Basic?
And
if this question belongs in a different newsgroup, please point me to
the
correct group.


.

Relevant Pages

  • Re: Recording the frequency of Yes/No Fields
    ... Create a query. ... That will eliminate all the records where the number as zero. ... Or you can put a text box on the report if you wish. ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.tablesdbdesign)
  • Re: "0.0" Value (not Null)... missing value in report
    ... Is Rate a calculated field in the query the report is based on? ... Sale_Price is never null or less than zero. ... the simple calculation report looks beautiful. ...
    (microsoft.public.access.reports)
  • Re: Conditional printing of records in the detail section of a rep
    ... I got sick, still sick, so I'm putting this on hold for the moment. ... In query design, type an expression like ... sums the numbers from approximately 35 numerical fields ... The default data is zero. ...
    (microsoft.public.access.reports)
  • Re: How to represent a record not found
    ... I left the database at work so I'll give these a try when I return. ... errors displaying on the report if it didn't find a record. ... >Another approach would be to start with a list (a query listing only those ... >convert a Null to a Zero. ...
    (microsoft.public.access.reports)
  • Re: Does DateDiff Have A Bug
    ... a calculated field in a query is the way to go here. ... But here's why I added the DaysRemaining ... I'm trying to write the result of lngDays when it reaches zero days ...
    (microsoft.public.access.formscoding)