Multiple Search Function

[sallieann]

I have a database with one table, and I would like to be able to search 3 of the fields within it. I have created a search form with 3 input boxes and a subform to display the results. I have managed to get it working perfectly except that it will only search for 2 values rather than all 3. How can I amend it to take it all 3 values? Any help is greatly appreciated. My existing code is as follows;


Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

If FieldValue <> &quot;&quot; Then

If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If

MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(42) & Chr(39))

ArgCount = ArgCount + 1
End If

End Sub

Private Sub search_Click()

Dim MySQL As String
Dim MyCriteria As String
Dim MyRecordSource As String
Dim ArgCount As Integer

MySQL = &quot;SELECT * FROM [stock] WHERE &quot;

AddToWhere [txtSearch1], &quot;[sap_code]&quot;, MyCriteria, ArgCount
AddToWhere [txtSearch2], &quot;[part]&quot;, MyCriteria, ArgCount
AddToWhere [txtSearch3], &quot;[part_details]&quot;, MyCriteria, ArgCount

If MyCriteria = &quot;&quot; Then
MyCriteria = &quot;True&quot;
End If

MyRecordSource = MySQL & MyCriteria

If Me![txtSearch1] <> &quot;&quot; Then
MyRecordSource = MySQL & MyCriteria & &quot; ORDER BY [sap_code]&quot;
ElseIf Me![txtSearch2] <> &quot;&quot; Then
MyRecordSource = MySQL & MyCriteria & &quot; ORDER BY [part]&quot;
ElseIf Me![txtSearch3] <> &quot;&quot; Then
MyRecordSource = MySQL & MyCriteria & &quot; ORDER BY [part_details]&quot;
Else
MyRecordSource = MySQL & MyCriteria & &quot; ORDER BY [sap_code]&quot;
End If

Me![search_results_subform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

 

[MakeItSo]

Hi salliann:
First of all: nice approach! ;-)
But: I guess your problem lies with having part_details as criteria and that is because it is text and not a number.

1)You have to enclose your search text of part_detail in wildcards and single quotes:
Instead of:
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(42) & Chr(39))
This should provide that:
If FieldName=&quot;[part_details]&quot; Then
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & &quot;'*&quot; & FieldValue & &quot;*'&quot; & Chr(42) & Chr(39))
Else
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(42) & Chr(39))
End If


Hope this solves it.
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[sallieann]

Hi Andreas, many thanks for your help - it works perfectly!