Hi guys out there,
There is this database Iam creating, I have a table with 40 fields among which there are Date/time, Text, Number, Memo and Yes/No fields and I have created the form bound to that tabe. I have also created Unbound form (for searching purpose) which is similar in layout with the bound one; in this search form the user will fill-in the keywords in as many text boxes as he wish to narrow down the search. The search results are displayed in the bound form (in a splitform view).
I have tried number of vb source codes I gethered from various sources from the internet they work well with text fields and other but they do not work with MEMO fields. The search on the MEMO field just returns nothing.
If anyone out there has a solution to this problem I will appreciate the assistance to make this project work.
The algorithm is: the code searches only the textboxes filled and discards the empty ones this is done after the user presses the search button. (Note: the fiels bears the same name in database table and Search Form)
The sample code is as follows:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
If Not IsNull(Me.txtCode) Then
strWhere = strWhere & "([txtRefCode] Like ""*" & Me.txtRefCode & """) AND "
End If
'The next code is for memo field (which fails to work)
If Not IsNull(Me.txtDescription) Then
strWhere = strWhere & "([txtDescription] Like ""*" & Me.txtDescription & """) AND "
End If
'Date field example next. It worked, but Iwasnt satisfied because I wanted the user to search for an exact date (which worked fine) or the operator and a date eg. > 2/27/2008 (which wont work with this code; suggestions requested.
If Not IsNull(Me.txtDateReported) Then
strWhere = strWhere & "([txtDateReported] = " & Format(Me.txtDateReported, conJetDate) & ") AND "
End If
'The code goes on for all 40 fields then finally:
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Where there was nothing in the string.
MsgBox "No criteria", vbOKOnly, "No Data Intered."
Else 'There is something there, so remove the " AND " at the end.
DoCmd.OpenForm "frmSearchResults"
DoCmd.Maximize
strWhere = Left$(strWhere, lngLen)
'Finally, apply the string as the form's Filter.
Forms!frmSearchResults.Filter = strWhere
Forms!frmSearchResults.FilterOn = True
End If
I came accross this code in the net and it was very helpful exept for the memo fields and date (which I need to use operators like >,< and <>.)
I am stuck here and desperate in need of resque. Anyone out there?
Thnx in advance
kmiraji@yahoo.com