Null Value Issue in Form to Query Build

[emhill57]

I have a form that creates two list boxes. The second list box contents are determined by what is selected in the first list box. This is working wonderfully. A valid entry in both list boxes can be a blank entry. When that gets passed as a parameter to my query it does not work correctly.



This scenario works correctly:


but when something like this is selected:


I get no results even though I know the data exists where the "Map Book" is equal to "3" and the "Map Page" is equal to blank or null.

Here is my SQL for my parameter query that is run when the "Run Query" button is pressed:

=====
SELECT tblSubTrans.SubDate, tblSubTrans.Instrument, tblSubTrans.InstrumentBook, tblSubTrans.InstrumentPage, tblSubTrans.SubCode, tblSubTrans.MapBook, tblSubTrans.MapPage, tblSubTrans.Addition, tblSubTrans.Block, tblSubTrans.LowLotNumber, tblSubTrans.HighLotNumber, tblSubTrans.Grantor, tblSubTrans.Grantee
FROM tblSubTrans
WHERE (((tblSubTrans.MapBook)=[Forms]![frmTestForm]![inp_mapbook]) AND ((tblSubTrans.MapPage)=[Forms]![frmTestForm]![inp_mappage]));
=====

I have a feeling that this has something to do with maybe the list box blank entry is null but the data in the table is not or vice-versa.

Any suggestions would be greatly appreciated!!!!

Thanks!

[ranman256]

you cant query nulls, to make a query that shows all records.
then in the continuous form , filter the results based on the cbos.
a button, btnFilter, click event filters

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[emhill57]

That's somewhat over this novice's head!

The two list boxes I have are built using two queries over table tblSubTrans.MapBook and tblSubTrans.MapPage. The first list lists all book values (unique) and the second list lists all page values (unique) based on what was selected in the first list. That works great. As you can see in my second image above a valid page value for a book value of "3" is blank. When my datasheet query runs, based on book=3 and page=blank, I get nothing even though that is a valid entry. I know it is something to do with nulls vs. spaces (maybe?) but your code above is very confusing.

Where does it go? What event is it based on? As I stated, I've been out of the Access loop for over 15 years so I'm really starting over. I have just now been given a major project (as I was the only person on staff that had even looked at Access in the past).

Thanks!!!!

[June7]

Why would a page value be blank? Shouldn't every book have at least 1 page? Could prevent the selection of Null.

ranman's code would go in an event, probably a button Click. His example uses city, state, zip. You would build criteria with book and page. Here is a tutorial reference on this technique: http://allenbrowne.com/ser-62.html

This does not use dynamic parameterized query (I have never used them). It sets Filter and FilterOn properties of a form or the WHERE CONDITION argument of OpenForm and OpenReport commands.

If your parameters were text type you could use LIKE and wildcard in the query but those will not work nicely with number values. Allen's tutorial shows a parameterized query with the conditional parameters.

[emhill57]

This data was given to us in a .txt file and then imported into Access. It is a Land Services company (abstracts, sub-divisions, etc.) so we are not going to mess with the data they gave us even if it does look wrong. This is some serious historical data. Some going back over 75 years.

I'll look at the link and see if I can get it through this thick skull of mine....

[June7]

So basically, if blank is selected, return all pages for the specified book? If you really want to return only book records that are blank in the page field, that is different issue.

Be aware Null and empty string are not the same thing. Need to verify 'blank' fields are Null or empty string.

[emhill57]

That is exactly it. Book value will be non-blank but the page value can be blank. Filtering the table by book = "3" and page = blanks:



Even more is both book and page = blanks:

[June7]

Well, define your rules and build the conditional code to fit.

[aytee111]

In your query, add a field for Forms!frmTestForm!inp_MapPage
For this field, in a second (new) line of criteria, add Is Null
Under the tblSubTrans/MapPage field, in the same line of criteria, add Is Null

This will have the effect of (your SQL above) WHERE ..... OR table's map page is null AND form's map page is null