Creating a Search Form

[Fusen]

Im trying to create a form that will search the records in a table according to multiple criterias. Ive been trying to do it this way:

If [txtBoxNo] <> "0" Then
LINKCRITERIA = "[BOXNO] = Forms![frmSelect]![txtBoxNo]"
End If

If [txtName] <> "0" Then
LINKCRITERIA = "[Name] = forms![frmSelect]![txtName]"
End If

If [txtDateIn] <> "0" Then
LINKCRITERIA = "[DateIn] = forms![frmSelect]![txtDateIn]"
End If

But it seems to bring in all records that fit any of the criterias. I'm also trying to create a date range search as well as "Like" Name search. Any help would be appreciated. Thanks!

 

[Mitch]

Ok, with the code your using the link criteria will simple be set to the last if criteria which was met. So you have test all three items in the if statement and have a resulting LINKCRITERIA for each. e.g.

If [txtBoxNo] <> "0" AND [txtName] = "0" AND [txtDateIn] = "0" then
LINKCRITERIA = "[BOXNO]=" & "'" & Forms![frmSelect].[txtBoxNo] & "'"
End If

Then have another If statement for if box and name <> 0 but datein = 0 until all options have been covered. Hope this sets you on the right track.

As for date range the set criteria for querys is Between [Value] And [Value2]. The same results can be achived be using
>= [Value]
<= [Value2]

Mitch.