Problem using null in forms and queries

[Guest]

I have a table of books. Books are characterized by author, title,
publisher, etc. On the the characterization columns is grade level, where
grade level equals 1, 2, 3, 4.

Only a few of the books have been given a grade level to date. For example,
of 100 books, only 10 have any grade level assignment. Therefore, 90 books
have no grade level.

I wrote a form (frmMain) that uses an unbound control (txtGrade) that allows
the user to search all the books according to the grade level. Of course,
the choices available are 1, 2, 3, 4, or "". The user then clicks a button,
which runs a query. So far so good.

Now here's the problem!

In the query, I wrote a little statement that uses the input from the form
to filter the query. Criteria = [Forms].[frmMain]![txtGrade]. I have also
tried Criteria = Like [Forms]![frmMainMaster]![txtPallet] & "*".
Additionally, I've tried including Is Null. When this is added, I get the
null books, but then I can't get any of the assigned books when I run the
form again!

The problem is that I can't get just the unassigned books to appear. That
is, if the user leaves blank the txtGrade box in the frmMain, then the query
still displays all the books- even those that have a grade 1, 2, 3 or 4!

How come I can't get it to show only the books with no grade assignment?

Does anyone have a solution?

Thanks so much!

 

[Allen Browne]

Instead of trying to use the criteria in the query, set the Filter property
of the form.

Set the After Update property of your txtGrade text box to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the Code window.
Set up the event procedure to look something like this:

Private Sub txtGrade_AfterUpdate()
If Me.Dirty Then 'save any edits
Me.Dirty = False
End If
If IsNull(Me.txtGrade) Then
Me.Filter = "[Grade Level] Is Null"
Else
Me.Filter = "[Grade Level] = " & Me.txtGrade
End If
Me.FilterOn = True
End Sub


Note: If Grade Level is a Text field (not a Number field), you need extra
quotes:
Me.Filter = "[Grade Level] = """ & Me.txtGrade & """"