Search Form throwing out rows that have a null

[Nick F]

I put together a search form with seven unbound text boxes.
Event Name: [txtEventName]
Fiscal Year: [txtFiscalYear]
Organizer: [txtOrganizer]
FiN Participation: [txtParticipation]
Month: [txtMonth]
Day: [txtDay]


Calender Year: [txtCalenderYear]

The underlying query has seven columns with Criteria in each.
Event_Name Like "*" & Nz([Forms]![Search_Form]![txtEventName],"") & "*"
Event_Month Like "*" & Nz([Forms]![Search_Form]![txtMonth],"") & "*"
Event_Day Like "*" & Nz([Forms]![Search_Form]![txtDay],"") & "*"
Event_Year Like "*" & Nz([Forms]![Search_Form]![txtCalenderYear],"") & "*"
Event_Partner_Organization Like "*" & Nz([Forms]![Search_Form]![txtOrganizer],"") & "*"
Event_Fiscal_Year Like "*" & Nz([Forms]![Search_Form]![txtFiscalYear],"") & "*"
Event_FiN_Participation Like "*" & Nz([Forms]![Search_Form]![txtParticipation],"") & "*"

Each field is not required so there are many rows that have at least one null. I am trying to use the NZ function to prevent access from tossing rows that have a null in them. It doesn't seem to be working. It is only returning records with all fields filled in. I have been looking for a bit and can't find where the issue. Any help would be greatly appreciated.

[orange]

Please post the query SQL.

[Nick F]

SQL as requested

SELECT Event_Main.Event_Name, Event_Main.Event_Month, Event_Main.Event_Day, Event_Main.Event_Year, Event_Main.Event_Partner_Organization, Event_Main.Event_Fiscal_Year, Event_Main.Event_FiN_Participation
FROM Event_Main
WHERE (((Event_Main.Event_Name) Like "*" & Nz([Forms]![Search_Form]![txtEventName],"") & "*") AND ((Event_Main.Event_Month) Like "*" & Nz([Forms]![Search_Form]![txtMonth],"") & "*") AND ((Event_Main.Event_Day) Like "*" & Nz([Forms]![Search_Form]![txtDay],"") & "*") AND ((Event_Main.Event_Year) Like "*" & Nz([Forms]![Search_Form]![txtCalenderYear],"") & "*") AND ((Event_Main.Event_Partner_Organization) Like "*" & Nz([Forms]![Search_Form]![txtOrganizer],"") & "*") AND ((Event_Main.Event_Fiscal_Year) Like "*" & Nz([Forms]![Search_Form]![txtFiscalYear],"") & "*") AND ((Event_Main.Event_FiN_Participation) Like "*" & Nz([Forms]![Search_Form]![txtParticipation],"") & "*"));

[orange]

I just reformatted the sql

SELECT Event_Name
, Event_Month
, Event_Day
, Event_Year
, Event_Partner_Organization
, Event_Fiscal_Year
, Event_FiN_Participation
FROM Event_Main
WHERE 
 Event_Name  Like "*" & Nz([Forms]![Search_Form]![txtEventName],"") & "*" AND 
 Event_Month Like "*" & Nz([Forms]![Search_Form]![txtMonth],"") & "*" AND
 Event_Day  Like "*" & Nz([Forms]![Search_Form]![txtDay],"") & "*"  AND 
 Event_Year Like "*" & Nz([Forms]![Search_Form]![txtCalenderYear],"") & "*" AND
 Event_Partner_Organization Like "*" & Nz([Forms]![Search_Form]![txtOrganizer],"") & "*" AND
 Event_Fiscal_Year Like "*" & Nz([Forms]![Search_Form]![txtFiscalYear],"") & "*" AND
 Event_FiN_Participation Like "*" & Nz([Forms]![Search_Form]![txtParticipation],"") & "*";

You have AND operators, indicating every search term should be used. Have you tried replacing the ANDs with ORs to see if that's what you're looking for?
There is a tutorial that may be helpful at http://www.datapigtechnologies.com/f...earchform.html

[Nick F]

I tried putting in OR but then it returns too many rows of data. I think it is then including all rows that contain a null value.

[orange]

Did you look at the tutorial? Was it helpful?
Can you post a dumbed down version of your mdb?

(no personal/confidential info)

[Nick F]

I did look at the tutorial and it turned out to be what I am basing my search form on. It just doesn't detail on how to deal with null values. Access won't let me save my current database in mdb format. Here it is in accdb.

[orange]

I would recommend against using Lookups at the table level
see:http://access.mvps.org/access/lookupfields.htm

Also, there is a video here that may be useful to you
http://www.datapigtechnologies.com/f...tomfilter.html