NZ() IsNull... What does it all mean?!

[ConfusedA]

Ok heres my problem, I'm building a search program that allows for multiple fields to be searched in one go. So I have a big query connected to a form with form objects associated to each field of the query...
For example:
There is a list-box for Job types: Road Reconstruction, Sanitary, etc.

In the query criteria for the "type" field I have: Like IIf(IsNull([Forms]![frmSearch]![lsttype]),"*",([Table-More info].[Type]) Like [Forms]![frmSearch]!

I cannot get results when a specific job type is clicked on :S.

Secondly, I have a fldyear field, with a corresponding textbox,
And Query Criteria:
Like IIf(IsNull([Forms]![frmSearch]![txtyear]),"*",[Forms]![frmSearch]![txtyear])
The problem is, some of my objects lack a year value and do not show up, my temporary solution has been to put "0" in this category, but I'd rather be able to leave it empty so it is less confusing, any way of doing this?

Any help is greatly appreciated.
Thanks.
A

 

[DCrake]

The Nz() function was introduced by Access to replace their NullToZero function, hence the abbreviation. The earlier code always returned a zero if a null value was encountered. However, the new Nz() function has been expaned to let the user specify what is returned if a null value is found. For example lets say you were doing a look up in a tabel to find the date a payment was made. It may be that the payemtn has not been made and as such the field actually contains a Null value. If you did a DLookup() it would return an error. vis

X = DLookup("PayDate","Payments","[Invoice]=11)

if invoice 11 was unpaid x= false

However if you coded it as such

X = Nz(DLookup("PayDate","Payments","[Invoice]=11),0)

X = 0

This would not return an error

Another example would be

Title = Nz("Title","Employees","EmployeeID=21","Mr")

Thus if no title was found in the title field it would return Mr as default

David

 

[Banana]

Dcrake already gave you examples of how Nz() works and this is the best solution for your scenario where you're using IIf(IsNull...

That said, generally you do not need to use IsNull() function in a query because that is VBA function. You want to use Jet's native check IS NULL or IS NOT NULL:

SELECT * FROM someTable WHERE someColumn IS NULL AND otherColumn IS NOT NULL;

 

[ConfusedA]

Thank you for your quick replies. It's still early in the day here and my brain isn't working at anywhere peak performance right now so these concepts are still a little fuzzy to me.

I think I understand the concept of NZ() but I do not understand how to make it work for my problem, same with the Select response....
I'm going to break this into a small number of questions. (Questions will be bolded)

I have limited my current search to 5 parameters.

#1. Type (Should never have a null field but it's search will not work if a field on the listbox is not clicked....currently in criteria i've made it [Forms]![FormType]![lstType] ...how can i make it show all values if this is not clicked?)

#2. fldyear, location, Consultant, Street .... some of these have null rows in their field. also I would like to be able to use the Like "* " function for these options because the aren't necessarily going to know the proper value to search for each item. How can I use select and or NZ() to successfully query these values?

Thanks again.
A

 

[ConfusedA]

Ok...heres what I'm trying to do...
I've set up two queries, one to create all the null values. The other to search based off the form.

In criteria for [txtyear] to change fldYear I have:
[txtyear] = Nz("[txtyear]","[frmSearch]","Null")

This is returning as containing the wrong number of arguments... I know I am missing
the equivalent of:
,"EmployeeID=21",
My problem is I want this to happen for all rows not just one, how do I cause this? and is the rest of my above line done properly?

 

[ConfusedA]

I decided my best bet is to make all the searchable fields required so that there would not be any nulls, I think this is the easiest solution. I'm going to work on setting up my queries and if I have any trouble getting them working properly I'll post to here.

Thanks everyone!

 

[Banana]

Missed your earlier post. Sorry.

The thing about optional parameter is that they requires a different SQL statement.

It may be possible to do something like this:

WHERE Iif(IsNull(<Some textbox>), 1=1, [SomeValue] LIKE <Some textbox>)

But the problem is that it get quite bloated & fragile not to mention that performance will be abysmal because it can't use index efficiently.

In the case where we want optional parameters, it's usually best to write a dynamic SQL:

Dim s As String

Const SQLPrefix As String =_
    "SELECT * FROM myTable"

If Len(Me.MyTextbox1) Then
   s = " someColumn = " & Me.MyTextbox1 & " OR "
End If

If Len(Me.MyTextbox2) Then
   s = s & " otherColumn = " & Me.MyTextbox2 & " OR "
End If

If Len(Me.MyTextbox3) Then
   s = s & " anotherColumn = " & Me.MyTextbox3 & " OR "
End If

If Len(s) Then
   s = SQLPrefix & " WHERE " & Left(s, Len(s)-4) & ";"
End If

You now have a SQL statement you can use to open a recordset based on the search with optional parameters.

Don't forget- if there's any strings or date, you need to delimit them as well.