MSAccess dynamic search form with multiple searchable fields and NULL values

Question

I have a form in my MSAccess application that searches the master database across 4 fields. You can fill in as little into 1 field or as much into all 4 fields or anywhere in between that you desire.

The database fields to be searched are:

• rmanbr - Integer
• custNbr - Integer
• customername - Text
• invcmnbr - Text (as it will store either invoice numbers (######) or credit memo numbers (CM-####)

The form has 4 fields in which to sort by respective of the above:

• SrchRMANbr
• SrchCustNbr
• SrchCoName
• SrchInvCMNbr

I have a query that selects all of the data to be displayed. The 4 fields to be filtered are filled with Like "*" & [forms]![RMA Search]![FieldName] & "*" into the Criteria section of the Query Builder for that specific query

The rmanbr and customername fields in the database will never be NULL, they're not allowed to be. But, the invcmnbr and custnbr can be and frequently are NULL with no values.

So, my search form is the 4 Srch fields listed above where users can enter in the stuff they want to filter by. There is a listbox that is designed to start filtering the results based on the "On Change" of the text boxes that the user will use to filter results. The user then selects the record and goes on his merry way.

However, the ListBox is filtering out all NULL Values from the two fields that can be NULL, whether or not the corresponding Srch box is blank, so the rows with NULL either in the custnbr or invcmnbr fields is not showing up in the listbox.

I've tried stuffing a Is Null into the "Or" Criteria of the query I'm using to populate the listbox. I tried it in the second line of the Query Builder Criteria section "Is Null" and this showed all the rows if they had NULL values even if I entered in a number into SrchRMANbr field. Ideally entering in an RMANbr would filter by RMANbr whether or not NULL values existed, since this is a unique value (there can only be 1 of any RMANbr in the master table). If I put after the Like "*" & [forms]![RMA Search]![FieldName] & "*" in the criteria (on the same line) Or Is Null it would get me closer, but any search into the SrchCustNbr or SrchInvCMNbr fields would produce the filtered result as well as all the NULL values for that field.

So, in short, I require a way to:

1) Show all of the values, NULL or not, in the listbox before a user starts to enter data in any field.

2) Filter away the NULL values when a user starts to enter data into the SrchCustNbr or SrchInvCMNbr field.

3) Keep the NULL Values up but filter by RMA Nbr correctly when the user starts to enter an number into the SrchRMANbr (as this is the master record, this is as specific as it can get)

I hope I'm conveying the issue correctly. Let me know if you need any additional information to assist me in solving my issue.

Answer 1

Using the solution posted here: MS Access: Ignoring query criteria if blank and pointed out by Andre

I've simply added Or ([forms]![RMA Search]![SrchCustNbr] Is Null) after the original Like "*" & [forms]![RMA Search]![FieldName] & "*" in both the CustNbr and InvCMNbr fields and this has worked.

Answer 2

As you are finding out, putting forms! expression in queries can get really messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE form. Often, I have a nice query that I could use MANY times for different reports, and often even that same query could be used for reports...but then someone comes along and puts in a expression that means the query is ONLY good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the user only selects restrictions in 3 of the combo boxes...and wants the other 2 to be ignore.

I could probably write another 10 or pages as to why putting forms expressions in queries is bad (besides...it makes the queries real ugly, and hard to read. and, the sql then is not standard anymore (it will not work with server based systems either).

So, the solution is to simply to take the values from the form, and build your own where clause in code. That way, you simply design the reports (or forms), and attached them to the query, BUT NO FORMS! conditions are placed in the query.

To "send" the conditions to the report (or form), you simply use the "where" clause. This is exactly why ms-access has this feature…and it solves a zillion problems…and will reduce your development costs by a substantial amount.

Take a look at the following screen shots to see what I mean:

http://www.kallal.ca/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the selected restrictions when you hit the "print" button is easy:

dim   strWhere       as string

'  select sales rep combo

if isnull(cboSalesRep) = false then

   strWhere = "SalesRep = " & cboSalesRep & ""

end if

' select what City for the report

if isnull(cboCity) = false then
   if strWhere <> "" then
      strWhere = strWhere " and "
   endif
   strWhere = strWhere & "City = " & cobCity & ""
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions you want. Lets say we have a check box to only include Special Customers. We can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
   if strWhere <> "" then
      strWhere = strWhere " and "
   endif
   strWhere = strWhere & "SpecialCust  =  true"
end if

For sure, each combo and control we add to the nice report screen takes a bit of code, but no more messy then the query builder..and this way, each query is nice and clean, and free of a bunch of HIGHLY un-maintainable forms! expressions.

Further, it means you can re-use the same query for different reports, and have no worries about some form that is supposed to be open. So, a tiny bit more code eliminates the messy query problem.. For me, this is very worth while trade.