Help me in my search code

[lyndonguitar]

Hi, I'm new here. I made a program for my mother for our business few years ago.(I self-studied a little bit of access) so far it has worked until recently.



Now she tells me it got a problem on searches, She typed some of the entries(it has thousands of entries) with multiple spaces in between. so if shes searching that item "word*single space*word", it would not show up because it was written as "word*multiple spaces*word"

I need it to be able to search for words that are separated by spaces individually, instead of searching everything in the text field as a whole. I forgot all about the codes and stuff so I need help please guys.

My current code, I dunno what to do

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmCustomers based on search criteria
Form_frmAsperMold.RecordSource = "select * from AsperMold where " & GCriteria
Form_frmAsperMold.Caption = "AsperMold (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If

End Sub

edit: i think i posted in the wrong section sorry

[June7]

Consistency in pattern is essential to search operations. Unless the number of spaces between words can be counted on to always be the same, there is no consistency. So if there is no reliable consistency in use of space character I suggest you clean up the data. This would involve repetitive UPDATE actions on the field using Replace() function. What do you suppose is the maximum number of consecutive spaces ever used - 5, 4, 3, 2? Do a Replace() UPDATE on each, starting with the highest.

UPDATE tablename SET fieldname = Replace([fieldname], "imagine 5 spaces here", " ")