I need to print N/A on a report when null value is returned.

[WildTaz]

I am trying to make a Purchase Order generator. Access and the reports are fine and working great if I am selecting to make a PO# for every store. But when I try to only make one or two PO#s and leave the rest blank, I get a blank report.

My tables are set up as such:
* poInfo - has all of the header info (Vendor, date and terms)
* osteenPO - has the actual po number generated. (one table for each store)

My report is a "form" layout that we used to fill out by hand. The Access form that I use to generate the POs has a sub-form for each store. I fill in the info and then select which stores that I want using a Yes/No, which then creates a new record in that store's table.

If anyone has any ideas as to how I can make the report print the N/A instead of simply printing a blank page, please let me know. I would be happy if the report would simply print. Thanks in advance for your help! :

-Todd

 

[THWatson]

WildTaz
I am having a little trouble visualizing your exact set-up. So, some questions...

1. Since you have a form/subform, can you not print individual purchase orders for individual stores, as needed? In other words, if you are filling in a purchase order for Store A, can you print a PO for that store while you are on the form?
To do so, you would have a standard report, linked to either the form or to a query.

2. The notion of getting a blank report unless Purchase Orders are filled in for all companies is where I am getting a bit murky in my thinking. Why would you want to print blank page reports?
Or is this a compilation report, a management type of report that lists what companies have made purchase orders?
If that's the case, and you have a main report plus subreports for each possible company, then you might want to take a look at using the HasData function to test for empty subreports.

example
=IIf([rptAcctPOSub].[Report].[HasData]=-1,[rptPOSub].[Report]![txtPO],"N/A")


Tom

 

[Crowley16]

eh, this report, presumably u've bound it to a query...

if that's the case, then just use a iif where you want the null's to be n/a's

e.g. SELECT iif(isnull(po), 'N/A', po) blah....

Crowley - as in like a Crow

 

[WildTaz]

The way that it works is like this. There is one table that has all of the information, and 6 tables that have the PO numbers. The form uses the Info feilds which apply an AutoNumber, and the Form has SubForms for each table with the PO Numbers. When you click on one of the Yes/No feilds in the SubForms it ties the AutoNumber from the Info Table to the PO Number (which is auto generated). If you do not select on of the stores, it does not create a record with the PO number tied to the info table record.

The report is our PO Agreement and a list of all stores. When I print the report, if all stores were not selected to have a PO number generated, then the report will print out blank. Not even info table will print on the report. If each store was selected in the form to have a PO, then the report prints fine.

I am trying to make it so that if all stores were not selected to have a PO number created, or in Access terms to have a record created, that it will print a "N/A" for that feild. The report has all feilds from the info table being printed, but only the PO number feilds from each store table being printed. There is a query running the report so that I can print only one "record set" at a time.

I am not sure if I am explaining this any better or not. Thank you for your help, and I do appreciate it!

*~* Tom, I hope this helped to clear things up a little bit.

*~* Crowley, What comes after the "Blah"? Is there more? I am very new to this and have never done any type of work with SQL, I am just sick of doing all of these things by hand. Since I have been working here for 3 years I have filled out over 4000 of these PO agrrements, and the corresponding log, YUK!

Thanks again all for any help you can give to me!

-Todd :

 

[THWatson]

Todd
I am thinking about how an IIf expression might be used in the query. Also about an Nz function in the report.

However, could you give a picture of what the report should look like? Would it be something such as...
Store Vendor Date Terms PO#
A Acme 6/1/04 30 days 123456
B General N/A

Or does each store have a subreport?

Tom

 

[WildTaz]

Tom,

The report is all one thing, no subreports. It is laid out with part of the Info Table's feilds at the top, and at the bottom. And then there are each of the Store Table's data in the center. I have done my best to give you an idea below.

The info table holds all the order info with fields like: OrderID, OrderVendor, VendorCode (look up field from other table), SalesRep, OrderDate, Cancel Date, Terms.

Each store table has: OrderID, Active (Yes/No), POnumber.

The report looks like this:

Vendor: ____________Acme Boots__________ VC: __ACM__

Store 1 PO:__12345__ Store 2 PO:__23456__
Store Information Store Information

Store 3 PO:__34567__ Store 4 PO:__45678__
Store Information Store Information

Store 5 PO:__56789__ Store 6 PO:__67890__
Store Information Store Information

Order Date:___06/09/04___ Cancel Date:___06/31/04___

Terms:_____Net 90_________ Shipping:_____Free_______

Sales Rep: ____Al_Bundy_____ Signature:______________
**Terms and conditions for incoming product.

Does this give you of an idea of what I am trying to do? Thanks again Tom!!

-Todd :

 

[THWatson]

Todd
That gives me a better picture. However, I am going to send you a private message, at the e-mail address you have indicated, and you can ship me a copy of your DB and I will be glad to have a look.

I'm sure there is a way to get what you want.
Tom

 

[THWatson]

Todd
I have been working on the database for a while this evening. From what I can see the problem lies with the queries.

For example, the query named POAform drives the POAPrint report, and criteria is supplied by the form OrderEntry...
When you use OrderID 19 as the criteria, the query comes up empty because a bunch of Null values come into play. Those Null values come from the subforms (Osteen, Daytona, Orlando and Altamonte) that don't have data.

Now, the question is: how to re-design things to take into consideration the situations where some of the subforms are empty.

I have to leave it for tonight. I'll continue to think about it tomorrow.

Tom

 

[THWatson]

Todd
I am putting this post here for the general benefit of the forum.

As I explained in my e-mail message, it appears that the problem laid in trying to make a query from several tables in which there could end up being Null values in an AutoNumber field.

I reconstructed the tables, changing the AutoNumber field to Number, and now a query is possible.

If somebody else knows of another way to work around this, perhaps they will add to our post.

Tom