Form/Query handling NULL values

[MLo]

Hello,



I have a form which refers to a query I have which looks at a couple of related tables.

Right now the form has a list box which shows the results of the query. The form also has a handful of text boxes, which, when data is entered into them, the query is re-run and the results in the list box are updated to reflect those values. The criteria in the query are mainly like this for several fields: Like '*' & [Forms]![FormName]![TextBox] & '*'. All of this works fine. However, one of the main tables the query is running against has some NULL values in some of the fields. For example, there is a date field and the query criteria is something like this: >=[Forms]![FormName]![DateTextBox].

The problem I'm running into is that any of the records that contain a null in this date field are being excluded from the list box even before any data is entered into the search box. I can certainly go into the query and modify it to include "OR IS NULL", however then when some data is entered in the text box it won't then filter out the null value rows from the list box.

I guess my question boils down to this: How can I get my query/list box to return all data for a given field until some data is entered into the search box, then, only display the data matching what was entered into the search box? Is this possible or am I trying to go about it the wrong way and there is some other better way to go about it? I'm open to suggestions.

Thank you for taking the time to read my question and for any possible help!

[June7]

I prefer not to use dynamic paramterized query. I use VBA to conditionally build filter criteria. If value is not entered in search control, then it is not included.

But if you want parameterized query, try: WHERE ([fieldname] >= [Forms]![FormName]![DateTextBox] OR [Forms]![FormName]![DateTextBox] IS NULL)

Review http://allenbrowne.com/ser-62.html

[MLo]

I prefer not to use dynamic paramterized query. I use VBA to conditionally build filter criteria. If value is not entered in search control, then it is not included.

But if you want parameterized query, try: WHERE ([fieldname] >= [Forms]![FormName]![DateTextBox] OR [Forms]![FormName]![DateTextBox] IS NULL)

Review http://allenbrowne.com/ser-62.html

Thank you very much for the response. It looks like, based on the example you've provided through the link that the entire data set is being built through the form and not referencing a query at all. I see the code you've applied to build the modifications to the criteria as it's filled out, and those are assigned to the filter buttons, so that's helpful. What I don't understand is if it's possible to apply this method to reference and update the parameters to the query that I've built?

Regarding your second recommendation, I've already tried that, but of course it then doesn't filter out the NULL values once some search criteria has been entered into the search box.

If you're interested to review what I've done so far, I've shared my database here: http://gofile.me/2UQG1/eDMapMUuA It's still in the build stage and doesn't have any important data in it yet.

I very much appreciate your reply and help so far. I'm just trying to build out a small personal project, but while I have some SQL background, I am not experienced with Access/VBA at all.

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[MLo]

So I went back and tried this: WHERE ([fieldname] >= [Forms]![FormName]![DateTextBox] OR [Forms]![FormName]![DateTextBox] IS NULL)
And although it seemed to me like it would do the same as what I already tried (adding OR IS NULL) the each variable in the WHERE statement, it did exactly what I was looking for it to do. I understand that this is looking at the field rather than the data in the table, but I still don't exactly understand why it works how I want it to.

Anyway, thank you so much for your help. I should've tried it sooner. Would've saved me a lot of hasstle.