I am running a report that I want to only show data where certin controls contain a Null value. I am using a from to enter the search criteria (a date) and I want it to only return the records that match the specified date and do not have information entered in for the mileage. This is what I am using:

SELECT tblMonthlyMaintenance.MonthlyMaintenanceID, tblMonthlyMaintenance.GSAID, tblMonthlyMaintenance.DivisionPOCID, tblMonthlyMaintenance.DueDate, tblMonthlyMaintenance.ManitenanceRequired, tblMonthlyMaintenance.Miliage, tblMonthlyMaintenance.Complete, tblMonthlyMaintenance.CompleteDate, tblVehicles.[Bumper Number], tbluShop.Shop, tbluDrivers.Name, [tbluDivision List].FullDivisionName, [tbluDivision List].DivisionID, tblVehicles.GSATagNumber
FROM (([tbluDivision List] INNER JOIN tbluShop ON [tbluDivision List].DivisionID = tbluShop.DivisionID) INNER JOIN (tbluDrivers INNER JOIN tblVehicles ON tbluDrivers.[Driver ID] = tblVehicles.[Driver ID]) ON tbluShop.ShopID = tblVehicles.ShopID) INNER JOIN tblMonthlyMaintenance ON tblVehicles.GSATagNumber = tblMonthlyMaintenance.GSAID
WHERE (((tblMonthlyMaintenance.DueDate)=[Forms]![frmsMaintenanceLog_Search]![Due Date]) AND ((tblMonthlyMaintenance.CompleteDate) Is Null));

When I run it without AND ((tblMonthlyMaintenance.CompleteDate) Is Null)); It works just fine. It is when I enter the rest of the condition that it returns a blank report. However when I run the query it returns the correct information.

Any ideas on what I am doing wrong?

Thanks!
Kat

Hi Kat,

Not sure if that makes sense. The result of the query depends on your data. If no records match the criteria, then the query will be empty.

Just my 2 cents...

The query is working when I run it, the problem is the report, which is based from the query is showing blank. If I remove the Null values the report populates.

Okay, let me see if I get it straight. If you put both criteria in the query, the query still shows you some records, correct? However, when you open the report, which is based on the query, the report comes up blank. Is that it? If so, try selecting the query from the Nav Pane and then click on Create > Report from the Ribbon and see if the new report still comes out blank. If not, then there's something else going on with your original report.

Just my 2 cents...

Ok, I redid the query (again) and it is working just fine. Maybe I was adding something so that was not returning the records I wanted in the report.

Thanks!
Kat

Hi Kat,



Glad to hear you got it sorted out. Good luck with your project.