By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

Searching Access by a Form with Textboxes and Display Results in Another SplitForm

P: 10
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
Apr 18 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.