468,244 Members | 1,727 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,244 developers. It's quick & easy.

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

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"
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
Apr 18 '08 #1
0 1235

Post your reply

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

Similar topics

6 posts views Thread by Michael Bulatovich | last post: by
6 posts views Thread by Peter Frost | last post: by
3 posts views Thread by Cillies | last post: by
4 posts views Thread by Megan | last post: by
7 posts views Thread by evilcowstare via AccessMonster.com | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.