Search Query

[bonusmarch]

I would like to know how do I perform a search using query (qryserch). I have 3 fields in the query which I want to perform a search. I have 3 checkboxes correspond to each search field. I also have a textbox which allows the user to type in what to search for based on the selected checkbox. When the user click on a search button, it will retrieve the matched records in a subform in datasheet view.

FirstName
LastName
ClassCode

E.g.

Dim strSQL As String

If Forms!Search!FirstName.value
strSQL = "Select FirstName, LastName, ClassCode
From T1
Where FirstName = Like "*" & Forms!Search!FirstName & "*"

DoCmd.RunSQL (strSQL)
End If

This is not the actual code I have, but just to get my idea through. My problem comes when performing the DoCmd.RunSQL, how do I run the sql statement into a specific query(qrySearch)?

 

[JamesMeadlock]

Ok... you have three fields from a query which you want the user to be able to specify 'like' function criteria for... your sub could go something like this:


(I'm giving you a simple, example, not an efficient one... and beware I just typed this in notepad... errors may be rampant... but it should give you a good idea of how to proceed.)

Private Sub SearchIt()
'
'
'
Dim lFirstName As String
Dim lLastName As String
Dim lClassCode As String
Dim mySql As String

' We assume if a textbox is blank, the user
' doesn't want to filter that field... so we
' say If thatfield Like ("*&quot Which will
' not constrain based on that field

If IsNull(Me.txtFirstName.Value) Then Me.txtFirstName.Value = "*"

If IsNull(Me.txtLastName.Value) Then Me.txtLastName.Value = "*"

If IsNull(Me.txtClassCode.Value) Then Me.txtClassCode.Value = "*"

MySql = "Select * From tblWhatever Where (" &
MySql = MySql & "(FirstName Like '" & Me.txtFirstName.Value & "') And "
MySql = MySql & "(LastName Like '" & Me.txtLastName.Value & "') And "
MySql = MySql & "(ClassCode Like '" & Me.txtClassCode.Value & "')"
MySql = MySql & &quot"

DoCmd.RunSql MySql

End Sub

 

[JamesMeadlock]

Umm in hindsight I only needed one variable... delete the 1st three.

I wish these forums would let you edit posts!

Also, using code like this, you'd might want to ditch the checkboxes, and give the users a cheat sheet of the valid like syntax... it'd give them a bit more control.

You could do the interface many different ways... this is quick & easy... if you have many users (or slow users), the checkboxes might be a good idea... along with good mouseover tips for the checkboxes & textboxes.

 

[bonusmarch]

Dim strSQL As String

strSQL = "SELECT RepositoryTable.LastName, RepositoryTable.FirstName, RepositoryTable.MI, RepositoryTable.ReportCode, ReportCode.AreaTitle, RepositoryTable.ClassCode, ClassCode.Classification, ClassCode.CBID, RepositoryTable.Change, RepositoryTable.DateLastUpdate
FROM ReportCode INNER JOIN (RepositoryTable INNER JOIN ClassCode ON RepositoryTable.ClassCode = ClassCode.ClassCode) ON ReportCode.AreaNumber = RepositoryTable.ReportCode
WHERE (((ClassCode.CBID) In ('C','M','S')));"

DoCmd.RunSQL strSQL

This is a sample of my code. My problem comes when executing the DoCmd.RunSQL strSQL. It gave me this error message "A RunSQL action reqires an argument consisting of an SQL statement. For example, an action query that appends records starts with INSERT INTO. A data-definition query that creates a table starts with CREATE TABLE." I just want to know how to run the SQL statement and show the result in a datasheet view.

And thanks for the prompt reply.