Conditional printing of records in the detail section of a report

[Ken Springer]

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.

Thanks.

Ken

 

[Allen Browne]

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]

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]

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

 

[Ken Springer]

Hi, Allen,

I apologize for the one day delay in answering your post, I've been glued to
the television watching the US Presidential election broadcasts.

I spent an hour or more writing a reply, as I was confirming everything I
was writing by checking it in Access. Then the system tells me the "system
is unavailable and try again later". I'm left with a blank window, and no
message to save a draft of.

Sorry, but "later" will have to be tomorrow, too angry at the "system" to do
it again tonight.

Ken

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Ken Springer]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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]

The short answer to your immediate question is, Yes: you need to build a
query with a calculated field. In query design, type an expression like
this:
TotalOffering: Nz([A],0) + Nz(,0) + Nz([C],0) + ...
using your field names in place of A, B, and C. You can then filter on this
expression.

The longer answer is that if you have 35 columns with similar values, you
have built a spreadsheet in Access. That's not how you design a relational
database. This should be 2 tables:
- one that contains whatever the main thing is (a person? a group? a date?)
- a related table that contains the various values in one field, but in many
records.
You can then solve the whole thing in several really simple ways (e.g. a
totals query.)

An unbound form just means one that has nothing in its Record Source.
Consequently it is not getting data from a table or query. You would not use
an unbound form if you want to save records; but if you need an interface to
display a comment, or get a filter value, it's useful.

If you want to work with the event procedures, you will need to buy some
books again. That fire sounds nasty.

I can't emphasize enough how important it is to learn about using related
tables rahter than repeating columns. That's absolutely crucial. If you get
that right, you avoid many headaches: no VBA workarounds can substitute for
a relational data structure. Post more info about this table if you don't
understand how to normalize it. Or perhaps the table analyzer can make
suggestions (Tools menu.)

 

[Ken Springer]

Hi, Allen,

LOL I was afraid I was right about the query. That will keep me busy for a
little while. And I figured more books would be necessary, no one supplies
manuals with their software anymore. Occasionally the PDF file on the CD's
somewhere. I find online help, either loaded on to my HD or, as MS does
these days, forces you to be online to get help access, essentially useless.

After the post, I went shopping, found MS"s Visual Basic for Access 97 and
ordered it. Shipping is costing 10 times the cost of the book.

I know the value of relational databases, and I wasn't offended by your
observation. I didn't create this database, it's something my brother-in-law
created. The very first iteration of it is so old..... It was started as a
Lotus 1-2-3 spreadsheet, then to Excel, and finally into Access. The first
database I ever created was on an Atari 8 bit.

Having the advantage of knowing what is in the database, I probably would
have created 3 tables. I've hinted about how they database could be much
more efficient, but it would require a couple simple changes no one wants to
tackle. Mired in the mud, shall we say.

I'm just trying to help him get the data out of Access. My observation is
people do not use a database program because Access is just about the only
one people know about as MS has just about driven everyone else out of
business or bought up the competition. And it's such a PITA to use.

I have a hunch the input form is the way it is since he's never figured out
how to get a report to give him the data he wants. So with the form he set
up, he can call up the record and print the screen. Really time consuming in
the long run.

If I could get my brother-in-law to switch to MS Works, it might be easier.

I've got other irons in the fire, but I'll see what I can accomplish on the
problem today.


Ken

The short answer to your immediate question is, Yes: you need to build a
query with a calculated field. In query design, type an expression like
this:
TotalOffering: Nz([A],0) + Nz(,0) + Nz([C],0) + ...
using your field names in place of A, B, and C. You can then filter on this
expression.

The longer answer is that if you have 35 columns with similar values, you
have built a spreadsheet in Access. That's not how you design a relational
database. This should be 2 tables:
- one that contains whatever the main thing is (a person? a group? a date?)
- a related table that contains the various values in one field, but in many
records.
You can then solve the whole thing in several really simple ways (e.g. a
totals query.)

An unbound form just means one that has nothing in its Record Source.
Consequently it is not getting data from a table or query. You would not use
an unbound form if you want to save records; but if you need an interface to
display a comment, or get a filter value, it's useful.

If you want to work with the event procedures, you will need to buy some
books again. That fire sounds nasty.

I can't emphasize enough how important it is to learn about using related
tables rahter than repeating columns. That's absolutely crucial. If you get
that right, you avoid many headaches: no VBA workarounds can substitute for
a relational data structure. Post more info about this table if you don't
understand how to normalize it. Or perhaps the table analyzer can make
suggestions (Tools menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Ken Springer]

HI, Allen,

I got sick, still sick, so I'm putting this on hold for the moment. I'm
going to wait for the book I ordered to come before getting back to this.
It's been shipped.

I'll post again when I get something figured out.

In the meantime, thanks for the help.

Ken

The short answer to your immediate question is, Yes: you need to build a
query with a calculated field. In query design, type an expression like
this:
TotalOffering: Nz([A],0) + Nz(,0) + Nz([C],0) + ...
using your field names in place of A, B, and C. You can then filter on this
expression.

The longer answer is that if you have 35 columns with similar values, you
have built a spreadsheet in Access. That's not how you design a relational
database. This should be 2 tables:
- one that contains whatever the main thing is (a person? a group? a date?)
- a related table that contains the various values in one field, but in many
records.
You can then solve the whole thing in several really simple ways (e.g. a
totals query.)

An unbound form just means one that has nothing in its Record Source.
Consequently it is not getting data from a table or query. You would not use
an unbound form if you want to save records; but if you need an interface to
display a comment, or get a filter value, it's useful.

If you want to work with the event procedures, you will need to buy some
books again. That fire sounds nasty.

I can't emphasize enough how important it is to learn about using related
tables rahter than repeating columns. That's absolutely crucial. If you get
that right, you avoid many headaches: no VBA workarounds can substitute for
a relational data structure. Post more info about this table if you don't
understand how to normalize it. Or perhaps the table analyzer can make
suggestions (Tools menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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]

I got sick, still sick, so I'm putting this on hold for the moment...

Look after yourself, Ken. Hope you're on top again soon.

 

[Ken Springer]

Hi, Allen,

Thanks for the well wishes, I'm feeling fine now and tackling the problem
again.

My problem now is, not finding enough of the "right" information. I'm a
book nut of sorts, and have lots of computer books never read.

Even with what I have for Office 97 and Visual Basic 5, I'm finding a
deficiency of useful information. With all the books I have, not a one of
them includes a list of the commands available in VB, or their syntax!

I finally found a list on the web, and now I've got something that connects
me to the Basic class I too so many years ago. That's going to be a big help.

Nice website for tips you've got.

Off to see if I can find out how to put that calculated field in to query
design!!!!! LOL

Have a great weekend.

Ken

Hi, Allen,

LOL I was afraid I was right about the query. That will keep me busy for a
little while. And I figured more books would be necessary, no one supplies
manuals with their software anymore. Occasionally the PDF file on the CD's
somewhere. I find online help, either loaded on to my HD or, as MS does
these days, forces you to be online to get help access, essentially useless.

After the post, I went shopping, found MS"s Visual Basic for Access 97 and
ordered it. Shipping is costing 10 times the cost of the book.

I know the value of relational databases, and I wasn't offended by your
observation. I didn't create this database, it's something my brother-in-law
created. The very first iteration of it is so old..... It was started as a
Lotus 1-2-3 spreadsheet, then to Excel, and finally into Access. The first
database I ever created was on an Atari 8 bit.

Having the advantage of knowing what is in the database, I probably would
have created 3 tables. I've hinted about how they database could be much
more efficient, but it would require a couple simple changes no one wants to
tackle. Mired in the mud, shall we say.

I'm just trying to help him get the data out of Access. My observation is
people do not use a database program because Access is just about the only
one people know about as MS has just about driven everyone else out of
business or bought up the competition. And it's such a PITA to use.

I have a hunch the input form is the way it is since he's never figured out
how to get a report to give him the data he wants. So with the form he set
up, he can call up the record and print the screen. Really time consuming in
the long run.

If I could get my brother-in-law to switch to MS Works, it might be easier.

I've got other irons in the fire, but I'll see what I can accomplish on the
problem today.


Ken

The short answer to your immediate question is, Yes: you need to build a
query with a calculated field. In query design, type an expression like
this:
TotalOffering: Nz([A],0) + Nz(,0) + Nz([C],0) + ...
using your field names in place of A, B, and C. You can then filter on this
expression.

The longer answer is that if you have 35 columns with similar values, you
have built a spreadsheet in Access. That's not how you design a relational
database. This should be 2 tables:
- one that contains whatever the main thing is (a person? a group? a date?)
- a related table that contains the various values in one field, but in many
records.
You can then solve the whole thing in several really simple ways (e.g. a
totals query.)

An unbound form just means one that has nothing in its Record Source.
Consequently it is not getting data from a table or query. You would not use
an unbound form if you want to save records; but if you need an interface to
display a comment, or get a filter value, it's useful.

If you want to work with the event procedures, you will need to buy some
books again. That fire sounds nasty.

I can't emphasize enough how important it is to learn about using related
tables rahter than repeating columns. That's absolutely crucial. If you get
that right, you avoid many headaches: no VBA workarounds can substitute for
a relational data structure. Post more info about this table if you don't
understand how to normalize it. Or perhaps the table analyzer can make
suggestions (Tools menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Ken Springer]

Hi, Allen,

Feeling much better.

I finally found out how to create a calculated field in a query. But then I
ran up against the (apparent) limit to the amount of text you can enter using
the Expression Builder.

So I thought, "make more than one query, then a query that accessed the
smaller queries." If that's possible, I haven't figured it out.

Also haven't figured out access the caculated field in the query from the
report.

And amongst this all, IE 7 first stopped having tabbed browsing, then quit
all together, wouldn't even run. IE 8 didn't want to run at first either.
And when I did get IE 8 to run, this reply window would open up blank.

If I had a job, I'd go buy a Mac and kiss Windows good bye.

Back to working on the criteria problem, unless you've got a solution.

Ken