Help with a Search

[Ryan Langton]

I'm designing my search form so that the user checks which field they want
to search using an option box containing option buttons. So they can
select:

All
Name
Serial Number
etc.

Then type what they want to search for in a text box and click "Search" (a
command button). I just need to clarify on my syntax since I'm not very
familiar with .adp yet and also some help with searching "all" fields. My
button-click event will look something like this:

SELECT CASE optSearchField
CASE 1 ' search all fields
' Unsure the best way to do this!

CASE 2 ' search name field
' Define stLinkCriteria and stForm
stLinkCriteria = "tblFieldName = '%" & txtSearchBox & "%'"
DoCmd.OpenForm stForm, , , stLinkCriteria

CASE 3 ' search Serial # field
' similiar to CASE 2
END SELECT

 

[Sylvain Lafontaine]

I don't use the LinkCriteria but I wonder if you must use the VBA syntaxe
(double quotes and *) for delimiting your txtSearchBox string instead of the
SQL-Server syntaxe (single quotes and %).

 

[Alex]

The way I do what you are trying is to build a sql select string based
on your selection, to search all fields this is a bit long winded, but
your select statement is going to look something like "select
myfirstfield from mytable where myfirstfield like'%" & txtSearchBox &
"%' or mysecondfield like '%" & txtSearchBox & "%'" having to use the
OR statement can get complicated on loads of fields. I use a form with
all the commonly used search fields each with their own textbox and
then a textbox for everything else then build my SQL select string
based on the vaules in each textbox, I then open my form with a
standard select statement that would never return any rows, after that
I use my select statement as the recordsource and refresh the form.


Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Candidate_ID, Title, First_Name, Surname, Email,
Postcode, County from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '%" & Me.Title.Value &
"%'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '%' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '%" &
Me.First_Name.Value & "%'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '%" & Me.Surname.Value
& "%'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '%" &
Me.Email_Address.Value & "%'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '%" &
Me.Ad_Postcode.Value & "%'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '%" & Me.Ad_County.Value
& "%'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"


then passing the SQL variable to the form.

 

[Ryan Langton]

Thanks Alex,

This is how I thought I'd end up doing it.

Ryan