Filter By Input Form Question

[Bill6868]

I have an input form with five fields of data that I want to use as parameters in a query to filter records; author, start date, end date, geographic location and text string. When the form is closed it saves the record to a temp table. When the form opens it deletes the temp table and opens up with a new record.

In my query I reference the temp table in the criteria field of the query, so they are all ands (no Or lines). This works great as long as all four fields are not null. My data form opens up with all four criteria being met and there is the record I am looking for.

What my users need to do is enter any combination of these four fields in the input form with any one of them being null. The null fields won’t work in the query especially since the start date and end date are “Between” in the query expression, and the text string expression reads Like (*)&[text string]&(*). A null value in these fields would be enough to cause havoc, but my query will not run if the author or the geographic location fields are null but the start date, end date, and text string have been entered.

Any suggestions as to what path I can take to a solution? I would be grateful.

Good grief!

 

[dhookom]

I would typically loop through the controls to build a complete SQL statement and use it to set the Record Source of the data form. You might also change the where clause in yor query to something like:

 WHERE ([Author] = Forms!frmFilter!txtAuthor Or Forms!frmFilter!txtAuthor Is Null) ....

If you can't figure out how to implement this, it is important that you provide your SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[Bill6868]

Thank you Duane. Yes, the implementation of your suggestion is a little over my skills, although I understand the drift. Below is the actual SQL statement from the query. Are you saying I would copy and paste the entire SQL statement into the record source property line of the form?

SELECT tblSigAct.RecID, tblSigAct.Author, tblSigAct.Day, tblSigAct.Month, tblSigAct.Year, tblSigAct.FullDate, tblSigAct.Geographic, tblSigAct.Title, tblSigAct.Text
FROM tblSigAct, tblVariable1
WHERE (((tblSigAct.Author)=[tblVariable1]![Author]) AND ((tblSigAct.FullDate) Between [tblVariable1]![StartDate] And [tblVariable1]![EndDate]) AND ((tblSigAct.Geographic)=[tblVariable1]![Geographic]) AND ((tblSigAct.Text) Like ("*") & [tblvariable1]![TextString] & ("*")));

 

[dhookom]

Try something like the following which should handle nulls in all but the date fields.

SELECT tblSigAct.RecID, tblSigAct.Author, tblSigAct.Day, tblSigAct.Month, tblSigAct.Year, tblSigAct.FullDate, tblSigAct.Geographic, tblSigAct.Title, tblSigAct.Text
FROM tblSigAct, tblVariable1
WHERE ((tblSigAct.Author=[tblVariable1]![Author] OR [tblVariable1]![Author] Is Null) AND ((tblSigAct.FullDate) Between [tblVariable1]![StartDate] And [tblVariable1]![EndDate]) AND (tblSigAct.Geographic=[tblVariable1]![Geographic] OR [tblVariable1]![Geographic] Is Null) AND (tblSigAct.Text Like "*" & [tblvariable1]![TextString] & "*" OR [tblvariable1]![TextString] Is Null));

Duane
Hook'D on Access
MS Access MVP

 

[Bill6868]

Duane - THANK YOU!!!! Those three little words was all I needed - OR IS NULL. Much appreciated!!!!

 

[Bill6868]

Duane - what I have noticed is that the first time I open up the database and type data in the input form I must enter all five fields of information, or it won't work. After that I can enter different data with null fields (as long as the start date and end date are entered). Not sure why this is happening. Would you have a clue?

 

[Bill6868]

Duane - please disregard previoius post. The problem was on my end. Thank you. Your suggestion worked perfectly. The US Army thanks you.