Filter using OR when one of values is null

[gg80]

I have a form with two pulldowns. The user can select number values in the pulldowns, then click a button to filter the form to either or both values. Problem is , if one of the pulldowns is null, I get an error message. I have an identical set up wherein the pulldowns are text values and do not get an error message when one is null.

I can put in some code to ignore one of pulldowns if it is null, but wonder if there is something simpler that I am missing. Any help much appreciated.

Code:

Me.FilterOn = True
Dim phrf1 As Long
phrf1 = Me.PHRFbox1

Dim phrf2 As Long
phrf2 = Me.phrfBox2



Me.Filter = "phrf=" & phrf1 & " Or phrf = " & phrf2 & ""

[June7]

Conditional code is probably best approach. Review http://www.allenbrowne.com/ser-62code.html

[gg80]

Conditional code is probably best approach. Review http://www.allenbrowne.com/ser-62code.html

Thanks for quick reply. I was hoping to avoid the extra code, but glad to know that I just didn't overlook something. . Funny that code is happy to ignore nulls with text values, but not with number values.

[June7]

Does your code for the text pulldowns use LIKE operator and wildcard?

[John_G]

Another solution might be to use the Nz function to give you a number (that you know will never match) when the pulldowns are Null. For example, if the pulldowns have only positive numbers to select from, then you could have the Nz return -1, like this:

phrf1 = nz(Me.PHRFbox1,-1)
and
phrf2 = nz(Me.PHRFbox2,-1)

When you declare a variable as anything other than Variant, and then attempt to assign a Null to it, you will usually get an error.

John

[gg80]

Solved. I had declared it as variant before and still got the error. However, the nz declaration works. The data does contain minus numbers, but never an entry less than -100 or thereabouts, so by using -300 instead of -1, problem solved Thank you very much.