Report Menu List Box Filter - Representing Null?

[misscrf]

I have a related thread here: thread703-1581216 if you want background.

I am using this method to create different report menus, which works great. Users can choose what type of report they want and then go to that report menu. If it is say a menu for Car Manufacturers, they have 2 list boxes. 1 for manufacturers and 1 for car models.

The list boxes are populated by just that field based on the main databse table for the info that has been filled in for all records (ie. some customer records do not have the car manufacturer or model filled out.)

Here is my challenge. I have this set up so that users can choose 1 or more of the values in either of the 2 listboxes and then click to get a subform (visible) with a grid (datasheet) layout that they can take to Excel or hit another button and get a formatted report.

This is the code that I have been working on:

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 If Me.LstCarMan = "(No Value)" Then
 strCriteria = strCriteria & _
 " AND isnull(txtCarManufacturer)"
 Else
 strCriteria = strCriteria & _
 BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
End If
If Me.lstCarName = "(No Value)" Then
 strCriteria = strCriteria & _
 " AND txtCarName isnull "
 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

The part I am struggling with is that there are many records where the manufacturer or model (name) is not filled out. I represent that in the listboxes by setting their rowsource to an iif statement that says if it is null, make it "(No Value)".

The tricky part comes in to the code above. When someone chooses "(No Value)" How do I make the query see that as null or nothing?

Any thoughts? I tried a few variations which is all in this part here:

If Me.LstCarMan = "(No Value)" Then
 strCriteria = strCriteria & _
 " AND isnull(txtCarManufacturer)"
 Else
 strCriteria = strCriteria & _
 BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
End If

But whether I do

 strCriteria = strCriteria & _
 " AND txtCarManufacturer is null"

or

 strCriteria = strCriteria & _
 " AND txtCarManufacturer isnull"

or

 strCriteria = strCriteria & _
 " AND isnull(txtCarManufacturer)"

I get no results on the summary. If I pick any other value, with or without (No Value) I get the results for those values.

Any help would be greatly appreciated!!!

misscrf

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

 

[dhookom]

You need to troubleshoot why the SQL isn't returning any records. Try add a line

Mysql = Mysql & strCriteria
[red]Debug.Print MySQL [/red] [green]'capture the sql[/green]
Me![frmSubCarQry].Form.RecordSource = Mysql

You can then grab the SQL and paste it into a new query. Otherwise you need to use this syntax for checking for Null

strCriteria = strCriteria & _
 " AND txtCarManufacturer is null"

You could also try:

strCriteria = strCriteria & _
 " AND txtCarManufacturer & '' = '' "

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I have been trying to debug my sql, but I can't seem to get it to give me the sql. It has been a while since I posted this, so I thought I would post my most recent code to see if anyone can help with the syntax.

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 If Me.LstCarMan = "(No Value)" Then
 Me.LstCarMan = "IsNull"
 strCriteria = strCriteria & _
 " AND txtCarManufacturer"
 Else
 strCriteria = strCriteria & _
 BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
End If
If Me.lstCarName = "(No Value)" Then
 strCriteria = strCriteria & _
 " AND txtCarName isnull "
 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

row source for the unbound listbox is this:

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]);

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.

Can anyone help me figure out the right syntax that will work?

Thanks!


misscrf

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

 

[dhookom]

I don't see this line in your code.

  Debug.Print MySQL 'capture the sql

Try this as a guess

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 If Me.LstCarMan = "(No Value)" Then
   strCriteria = strCriteria & _
     " AND txtCarManufacturer Is Null "
  Else
   strCriteria = strCriteria & _
     BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
 End If
 If Me.lstCarName = "(No Value)" Then
   strCriteria = strCriteria & _
     " AND txtCarName is null "
  Else
   strCriteria = strCriteria & _
     BuildIn(Me.lstCarName, "txtCarName", "'")
 End If
 Mysql = "SELECT * FROM qryCarQuery Where "
 Mysql = Mysql & strCriteria
 Debug.Print MySQL  'put the value in the debug window
 Me![frmSubCarQry].Form.RecordSource = Mysql
 Me.frmSubCarQry.Visible = True
End Sub

You need to understand the criteria are joined with "AND" not "OR".

Duane
Hook'D on Access
MS Access MVP