Dealing with Null values in select query

Hi Army, I will try to help.

What is the purpose of this form? Is there code behind the form? If so, what is it? What is the Controlsource of the control bound to the PRT field? Where are you using the NZ() function?

It looks like you are trying to populate the the PRT# based on a value entered into a control on the Main Menu form. based on that, I do have some suggestions.

1. Use a combobox on the Main Menu to select from the valid values. In that way the query will always return a PRT# and never be Null. Thus avoiding the problem.
2. Use code like this:

If DCount("*","queryname") = 0 Then

 Me.controlname = "N/A"

Else

  Me.controlname = DLookup("[PRT#]","queryname")

End If

If you need further clarification for this feel free to ask.

Ok, you edited your response to add the info I'm seeking. So one thing you can do is change the line that gives you the error to:

partnum = Nz(Forms![Part num search]![PRT#], "N/A")

That should fix the issue.

However, I think you can do this more efficiently. Instead of the code you have behind the Part Number Search button. I would use this code:

Dim tmpPRT as Tempvars

If DCount("*","queryname") = 0 Then

Tempvars!tmpPRT = "N/A"

Else

Tempvars!tmpPRT = DLookup("[PRT#]","queryname")

End If

Once you have assigned the PRT to a Tempvar, you can do anything you want to it like assign a control on a form to it.

One last suggestion. Don't accept the default names for controls. Text14 is meaningless. It will make your life easier if you assign meaingfull names like txtPRT instead.

I would add to this line

partnum = Forms![Part num search]![PRT#] & “”

Yes it helps me to understand what you are doing, but it doesn't change my suggestions. You say you click your "Event Procedure", That indicates you are running some code, so it would help to see what that code is.

You do not have to actually run the query. If you use the code I gave you in option 2 as your On Click event procedure, then it will do what you want.

Another option. since you say you get the Matrix from another form, you can have some sort of indicator on the other form, to indicate whether a PRT# exists for that Matrix, so you don't even have to open the form you are populating if it doesn't. If that interests you I can suggest ways to do that.

As stated I have a select query that searches a Table that has two variables; PRT# and Matrix. I obtain the Matrix

from a form and then search the Table for this Matrix. If the Matrix exists, then I copy the PRT# over to show the
PRT# and Matrix. But, if the Matrix does not exist, then the PRT# becomes null and I receive an Invalid use of Null
error. I have no idea on how to just create a message or something saying "PRT# not found" or "N/A" I'm not picky here.

So, to answer your question the best way I know how, I copy and past a Matrix into a box and click my "Event Procedure"

which opens up "Find Part Num Query" which is the query above. Does that help? Here are the details -

I copy and paste a "Matrix" into this form -

When I click on Part number search, I fire up the below code:

Dim Matrixsrch As String

Dim partnum As String

Matrixsrch = ""

partnum = ""

Matrixsrch = Me![Text3] <----This is where I copy and paste my matrix

' This is where I search my main data table for any existing part number

DoCmd.OpenForm "Part num search", acNormal, , , , acHidden

partnum = Forms![Part num search]![PRT#]

DoCmd.Close acForm, "Part num search"

If partnum > "" Then

Me![Text14] = partnum

Me![Text14].Visible = True

Me![Label16].Visible = True

End If

If partnum = "" Then

Me![Label17].Visible = True 

End If

So, in the above, I always receive an Invalid use of Null error on the line:

partnum = Forms![Part num search]![PRT#]

Just not seeing what I'm doing wrong here?