Use an iif statement to deal with null values in criteria boxes

[Gary]

Hey All,



I did some research on this but never found a satisfactory answer.

Why is it that in the query builder when I enter the following into the criteria box it doesn't deal with the null in the combo

iif( isnull(forms!formname!combobox), is not null ,forms!formname!combobox)

shouldnt this return any feilds that are not null???

Thanks in advance,

Gary

[jzwp11]

A query is looking at a field in a table. So if you want to find records where a certain field is null then you have to reference the field not the combo box.


WHERE field1 is not Null

You can use a form control to supply values to a field used in the criteria in a query

WHERE field2=forms!formname!combobox

Also this function is not valid, since "is not null" does not represent a valid value or expression that can be returned by the IIF() function.

iif( isnull(forms!formname!combobox), is not null ,forms!formname!combobox)

[Gary]

all I want is a simple way to display all records that are not null if a combo box value is null. if the combo box is not null i want to display all records with the value in the combo box.

I have even tried using the following

iif( isnull(forms!formname!combobox), not forms!formname!combobox ,forms!formname!combobox)

I just need something to put in the spot for if the is null statement is true

is there any function that I can use to do this?

-Gary

[jzwp11]

This might work for you. You'll have to supply your specific names :

IIf(IsNull(forms!formname!combobox), True, table1.field1 = forms!formname!combobox);

[maximus]

I am not an expert as jzwp11 but I would have done some thing like this:
Use CreateQueryDef to Dynamicaly create my Query

Here is the Code:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim strSQl As Object
Dim strDropQry As String
Dim strCriteria As String
If IsNull(Me.Combo0) Then
strCriteria = "MemberName Like '*'"
Else
strCriteria = "MemberName='" & Me.Combo0 & "'"
End If

strDropQry = "Drop table MyQuery"
On Error Resume Next
CurrentDb.Execute strDropQry, dbFailOnError

Set strSQl = CurrentDb.CreateQueryDef("MyQuery", "Select * From tblmember Where " & strCriteria)

DoCmd.OpenQuery "MyQuery"

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

I have used strCriteria to determine the criteria of the query when the ComboBox 0 is Null I have used criteria Like '*'

When the combo box has a value that is taken then as the criteria to create the query.

I have used a drop table sql to drop the Existing MyQuery this assures that Myquery is always according to the latest Criteria

Hope this helps

[jzwp11]

maximus,

I am not an expert by any means. In fact, I was thinking along the same route as you since I would probably prefer the code solution. But before posting a similar code, I went ahead and did some internet searching. I actually found the solution on another site. I did check it in a test database, and it appears to work.

[Gary]

hi jzwp11,

I tried to use the solution you posted:

IIf(IsNull(forms!formname!combobox), True, table1.field1 = forms!formname!combobox);


in the criteria for the query builder and when the query was run the variable box popped up asking what table1.field1 was supposed to be.

when I removed table1.field1 I got a error saying the query was too complicated to be solved.

I will try maximus' solution next.

-Gary

[Gary]

JUST GOT IT!

instead of using the IIF I did the following

Like NZ([forms]![Form1]![comboBox],"*")

Works Beautifully!!

Thanks for all your help guys!

-Gary

[Gary]

How do I mark this as solved??

[jzwp11]

table1.field1 represents whatever table/field that you are using to compare the combo box value to. You would have to substitute your own table/field name here.

I could not find how to mark a thread as solved. It might only be an option for the original poster.

[maximus]

you can mark the thread solved by clicking on thread tools on the top right hand side of you screen.

To learn how to do it follow this link.

https://www.accessforums.net/forum-s...lved-1828.html

I am glad that the problem is solved and in a much simpler manner.

[Gary]

thanks Maximus, I guess I just wasn't looking hard enough!

the only problem with the solution is that it doesn't deal with null values in the table I am querying. now that I think of it I can probably do something like:

LIKE NZ([forms]![Form1]![comboBox],"*" or IsNull)

If that works I will post to let you know.

[smartie]

Hello, I believe my issues are related to your threads.

I have a form with 1 unbound combo box. The row source is pulling it from the tblOffices, which shows a list of all of our offices within the company.

I have 1 command button, the event procedure (on click) opens a report (do cmd) "rptEvaluations". The rptEvaluations is based on the qryEmpEvaluations. The query has the following fields tblEmployee.office, tblEmployee.name, tblEmployee.EvalType, tblEmployee.EvalDate

So basically I want the user to select an office and click the button to report on every employee for that specific office space.

The criteria I have entered into the qryEmpEvaluations.Detachment is: "[Forms]![frmReports]![cboOffice]"
This code works, it brings up the report and only displays the employees for the office specified.

I would like it to display all offices and all employees if the combo box is left null.

thanks,
dave