How can I print a client detail record from my Microsoft Access Report, only if the "Date Removed" field is blank or null?

I have created a client "report" in Access that prints the client information.

The client information records contain:  "Document Name", "Document Date" and "Date Removed".

I want to give my client a detailed report of documents that are located in my office.  Any document that has information in the "Date Removed" field, means the document is no longer in my office, and thus, I do not want it to appear on the report.  

Prior to printing a detail line, I want the report to check the "Date Removed" field.  If the field IS blank or null, then print the "Document Name" field, "Document Date" field on the detail line, otherwise, do not print the line.

Without using a query, I think I can build an expression (inside the reports "detail section") to exclude "Date Removed" records, but unsure how to do it.

I am using Microsoft Access 2002 version.

Thanks for your help.
eemmpphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Why would you want to avoid using a query as the RecordSource for the report?  That would be the obvious and simple solution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eemmpphAuthor Commented:
On my client form, I am able to click "find a client".  It then brings up the client I am looking for.  I click a command button to Print the Client Report.

In the report design:

The record source is "Select DistinctRow [ClientTable].[ClientNumber], [ClientTable].[ClientName], [DocumentTable].[DocumentName], [DocumentTable].[DocumentDate], [DocumentTable].[DateRemoved].

This prints ALL the client records, includijg those where "dates" appear in the DateRemoved field.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
This prints ALL the client records, includijg those where "dates" appear in the DateRemoved field.
that's for default feature.

so for the "find a client" button, you got to add in the condition to filter out where DateRemoved is not null which means make a condition where DateRemoved is null.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
The report's RecordSource query has no criteria.  You will have to add it in the command to print the report.

DoCmd.OpenReport "myreportname", acViewNormal, , "ClientNumber = " & Me.ClientID & " AND DateRemoved Is Null"

Don't forget to substitute your report name.
eemmpphAuthor Commented:
I tried this:  
DoCmd.OpenReport "myreportname", acViewNormal, , "ClientNumber = " & Me.ClientID & " AND DateRemoved Is Null"
and received an error when this is added to the DoCmd:  & " AND DateRemoved Is Null"


There are 2 tables:
 
"Clients" Table - containing fields:
          ClientID (indexed, no duplicates) one-to-many relationship
          ClientName
 
"Documents" Table containing fields:  
          ClientID (indexed, duplicates ok)
          DocumentName
          DocumentDate
          DateAdded
          DateRemoved
          Disposition
PatHartmanCommented:
It is always best if you post your actual code rather than type a reference to it.  We can't see what you are actually running and so we can't tell if there are any typos. You also didn't way what the error was so we don't have any idea what problem you are experiencing.

Please post the exact code and the error number and message.
eemmpphAuthor Commented:
I have decided to use a query as suggested by Pat and also follow Ryan's suggestion.
eemmpphAuthor Commented:
My query is working perfectly!  Thanks for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.