Listbox - represent null, filter for it and/or other values? 1

[misscrf]

I have a series of report menus that allow users to filter based on list boxes and other fields. Once they choose any combination of filters for the controls on the form, they can hit a Summary button to see a subform become visible with a datasheet grid view (easy to paste in excel or an email for quick answers to questions).

The issue I am working on is that my list box needs an option for if there is no value in the field. Users may want to check to see which haven't been filled out yet. In this example, we are choosing from car manufacturers, models and such.

This is the row/source of the unbound list box on the summary/report form menu:

SELECT IIf(IsNull([txtCarManufacturer]),"(No Value)",[txtCarManufacturer]) AS CarMan FROM qryCarQuery GROUP BY IIf(IsNull([txtCarManufacturer]),"(No Value)",[txtCarManufacturer]) ORDER BY IIf(IsNull([txtCarManufacturer]),"(No Value)",[txtCarManufacturer])

That makes it so the end user sees (No Value) in the listbox as a potential option to choose.

Then, I have this code on the onclick of the Run Summary button:

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 [COLOR=#ff0000]If Me.LstCarMan = "(No Value)" Then
 Me.LstCarMan = "IsNull"[/color]
 strCriteria = strCriteria & _
 " AND txtCarManufacturer"
 Else
 strCriteria = strCriteria & _
 BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
End If
[COLOR=#ff0000]If Me.lstCarName = "(No Value)" Then
 strCriteria = strCriteria & _
 " AND txtCarName isnull "[/color]
 Else
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarName, "txtCarName", "'")
End If
 Mysql = "SELECT * FROM qryCarQuery Where "
Mysql = Mysql & strCriteria
Me![frmSubCarQry].Form.RecordSource = Mysql
Me.frmSubCarQry.Visible = True
End Sub

I have been playing around with the part in red, but I cannot seem to figure out the syntax to allow the users to choose null as an option when running this. Another potential issue I am seeing is that I need to build this so that a user might just choose "(No Value)" or they might choose that and other values. If a user chooses no filter, they get everything.

Right now, with the code above, if I choose just (No Value) I get no results (but I know there are nulls for some records).

If anyone can help me with the syntax to make this work, that would be great.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Why not change the SQL of qryCarQuery so that "(no Value)" is returned as the Manufacturer.

  CarMfg: Nz(txtCarManufacturer,"(No Value)"

Then change your code to:

 BuildIn(Me.LstCarMan, "CarMfg", "'")

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

You are a genius!!!! This is the Occam's razor solution that I should have come up with a LONG time ago!!!


Star, star, star!!!

misscrf

It is never too late to become what you could have been ~ George Eliot