How do I ignore null fields in my report? Query? 1

[DesertTrip]

I have a query that includes an field of multiple choice/optional entries (from a look-up table). Let's say (for example) that this particular field is "TypeOfPets".

I need to make a Report that will use ONLY the records that have information entered in "TypeOfPets". If the user did not make an entry in that field, I do not want any portion of the record included in the report.

At this time, my reports keep including blank data and including blank records in the detailed sumamry count of records.

Example of how it is coming out in my attempts:

5 detailed records (for the five blank records)
Cats 2 detailed records
Dogs 3 detailed records
Total records = 10

What I need out of it:


"Cats 2 detailed records
Dogs 3 detailed records
Total records = 5"

I am guessing there is some criteria I need to put in that particular field of the Query, but when I used a null equation, it wiped out ALL records from the report. Maybe I did the null incorrectly?

I have tried F1, my books, etc and I am guessing it can be done. Can't it?

 

[FoxProProgrammer]

Hi DesertTip,

It sounds like you need to set the RecordSource for the Report to a Query that filters records that have a null TypeofPets.

Have you tried something like this for the RecordSource?

Select table.TypeofPets From table Where Not IsNull(TypeofPets);

If you want to be sure that the Query is set up right, you can test it in the Query Design Window to see what records are returned before you use it in the Report. dz
dzaccess@yahoo.com

 

[DesertTrip]

Hi FoxPro and thanks, but I am thinking I probably didn't give enough information. I apologize for that. I will still need those records to query up for other parts of the report.

Inthe first section of my report, I want all pieces having to do with the (for example) "type of pets" to include records only with entries for that particular field.

But, I will still need the data in the remaining fields of those same records for another part of the report, regardless as to whether or not they made an entry for the "type of pets".

I was wondering if I could do this somehow on the report itself. For example, could it be written to sort out all null records on one field of the report.....while elsewhere in the report, still pull data from ALL the records, regardless as to whether or not they made an entry for every field?

Yes, I am relatively new to Access....I can do some expressions; criterias; math statements; etc. But I am pretty much lost in the more complex customizations and to make matters worse, the four Access manuals I have are useless.

Am I trying to do the impossible?

 

[FoxProProgrammer]

Hmm, thanks for the clarification. I understand what you want to do now, but I don't know how to do it. It almost sounds like you need two recordsets, one that contains all records, and one that contains records that don't have a null TypeofPets. I was thinking that you could create multiple recordsets for the different parts of your report, but I couldn't get it to work. I tried to Dim two recordsets in the Report, and Set them in the Open event. The record sets were created properly (I verified their contents), but I couldn't figure out how to get the data from the recordset into a field in the detail section of the report. If there's a way to do that, you might be able to use this technique, but I have to admit that I'm just guessing here. Here's the code if you want to mess around with it.

Option Compare Database

' Scope these to the Report.
Dim db As DAO.Database
Dim rsNonull As DAO.Recordset
Dim rsAll As DAO.Recordset


Private Sub Report_Open(Cancel As Integer)

Dim SQLnoNull As String
Dim SQLAll As String

SQLnoNull = "Select Manufacturers.City From Manufacturers Where Not IsNull(Manufacturers.City);"
SQLAll = "Select Manufacturers.City From Manufacturers;"

Set db = CurrentDb
Set rsNonull = db.OpenRecordset(SQLnoNull, dbOpenDynaset)
Set rsAll = db.OpenRecordset(SQLAll, dbOpenDynaset)

___

In the detail section of the report, I tried to refer to the data with rsNonull!City and rsAll!City. I got the #Name error when I ran the report.

Just wondering, do you have to do all this in the same report? It would be a lot easier to run a report for the records that don't have a null entry, and another report for all records. If you use a subreport, you can set a different record source, but I don't think it will work in this case because a subreport is set up for parent/child relationships. This is an interesting problem, and I'd be interested if you figure out how to do it. dz
dzaccess@yahoo.com

 

[DesertTrip]

Thanks for trying and devoting some time to it. I appreciate it very much.

This report is actually a productivity report for the County department I work in. I have to track several different types of hourly tasks. The users will be inputting their tasks and projects hourly/daily. This is feeding into my table which is feeding the query.

The dilema is that some records will contain only State paid projects and some will contain County tasks. BOTH of these types of time records are required in the finished productivity report....

The State project fields are not always entered (they have the occasional null value in the time record....if a county task is completed instead)

I originally had two queries (State and county) but was told by an Access guru to condense to one for the report. That guru is now working elsewhere. I am an Excel guru (did all our county forms and budget automation) who got stuck on this task until it is completed and I am sooooo close. This is my last hurdle.

I am open to all suggestions (written simply if at all possible).

 

[scriverb]

Try this: In the OnFormat of the Detail Section put the following code:

If IsNull(me![TypeOfPets]) then DoCmd.CancelEvent

This will stop the printing of the rows where there is no data in the control Type of Pets.

Bob Scriver

 

[DesertTrip]

ScriverB - It worked!!!!!!!!!!!!! Wow! I wasn't even sure I put it in the correct place as I couldn't find OnFormat. (Told you I was new) So, I put it in the FORMAT field on the properties menu or the report field (Sorry I don't know all the correct terms for these), but it worked. Was that the same place you were describing to me???

Either way - Thank you! Thank you! Thank you!

 

[DesertTrip]

TYPO Correction - "So, I put it in the FORMAT field on the properties menu of the report's field"

 

[scriverb]

I think you got it in the right place. You can get there by right clicking on the Detail Section line and select the Properties item. Then at the bottom of the window is an OnFormat item. Click the down arrow and select Event Procedure. Then click the 3 dots( . . .) at the right and that will take you to the VBA code subroutine for this item. It is here that the code should go. I am betting that it is already there because you got it to work.

Good luck. Thanks for the Star!!!

Bob Scriver