Search Form issues

[Island_Dude]

I have a search form with multiple criteria, It works fine with values;
however, if I don't enter any value and I click on search, the prompt
that supports to alert the user to enter at least one criteria is not
working. Any advice:

Private Sub cmdSearch_Click()

On Error GoTo error_handler

If Not IsNull(Me.txtRED & "") And Not IsNull(Me.txtBLUE & "") And Not
IsNull(Me.txtORANGE & "") And Not IsNull(Me.txtPINK & "") Then

DoCmd.OpenForm "frmCOLOR"

Else

MsgBox "Please enter at least one criteria.", vbCritical +
vbOKOnly, "No Criteria"

End If

Exit_Here:
Exit Sub

error_handler:
Select Case Err.Number & vbCrLf & Err.Description
End Select
Resume Exit_Here

End Sub

 

[Guest]

Try to add your field names in the form like this:
If me.FieldName1 is not null and me.FieldName2 is not null then - - - -
Good luck!

 

[Douglas J. Steele]

By concatenating "" to the field, you're guaranteeing that it's not going to
be Null.

Surprisingly, it's more efficient to determine the number of characters than
whether it's Null. Try the following instead:

If (Len(Me.txtRED & "") + Len(Me.txtBLUE & "") + Len(Me.txtORANGE & "") +
Len(Me.txtPINK & "")) > 0 Then

 

[Douglas J. Steele]

In VBA, you need to use the IsNull function. Is Null (and Is Not Null) is
only for SQL.

See my other post for a slightly better approach (plus an explanation of why
Island_Dude's code wasn't working).

 

[Island_Dude]

Thank you Mr. Steele,

That technique works perfectly.