search

**simba** · 08-23-2011, 05:30 AM

I have a requirement of searching the database based on 3 columns. I have attached the search form and the code. Not sure, how to fine tune this. Can someone help?

Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

strJoin = " AND "
strSQLHead = "SELECT * FROM QryResourceContacts "

If Len(Me.txtZipCode & vbNullString) Then

If (Me.chkLike) Then
strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.cboCountyName & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [CountyName] Like " & Chr$(39) & "*" & Me.cboCountyName & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [CountyName] = " & Chr$(39) & Me.cboCountyName & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.cboTypeOfService & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [TypeOfService] Like " & Chr$(39) & "*" & Me.cboTypeOfService & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [TypeOfService] = " & Chr$(39) & Me.cboTypeOfService & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(strSQLWhere) Then
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
End If

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

Me.LstRecords.RowSource = strSQL

End Sub

**Robeen** · 08-23-2011, 07:49 AM

Have you tried putting a break point
[left-click in the gray margin to the left of the code window]
in your code on this line:

Code:

 
Me.LstRecords.RowSource = strSQL

and then hovering your mouse over the strSQL?

OR - put:

Code:

 
MsgBox strSQL

just before that line of code.

That way you get to see the SQL statement that you are generating in your code and you will get a better idea if it is exactly what you need.

What problem are you having with your SQL?

**simba** · 08-23-2011, 10:21 AM

Thanks Robin for your reply. Yes, I do check using breakpoint.

The keyed in zipcode and the other two parameters are captured in the breakpoint correctly.

But my problem is, it is not filtering the list based on the search terms.

Need assistance, with that.

Thanks once again, in advance.

**Robeen** · 08-23-2011, 10:25 AM

Does your constructed SQL 'Statement' have correct SQL syntax?

Can you post the exact SQL statement that is coming out of your code? I'm wondering if that might be the problem.

**simba** · 08-23-2011, 11:09 AM

Robeen! This is the code that I have. Do not know to interpret it.

Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

strJoin = " AND "
strSQLHead = "SELECT * FROM QryResourceContactsColumn "

If Len(Me.txtZipCode & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.txtCountyName & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [CountyName] Like " & Chr$(39) & "*" & Me.txtCountyName & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [CountyName] = " & Chr$(39) & Me.txtCountyName & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.txtTypeOfService & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [TypeOfService] Like " & Chr$(39) & "*" & Me.txtTypeOfService & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [TypeOfService] = " & Chr$(39) & Me.txtTypeOfService & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(strSQLWhere) Then
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
End If

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

Me.LstRecords.RowSource = strSQL

End Sub

**Robeen** · 08-23-2011, 11:20 AM

Try this:

Code:

 
Debug.Print strSQL

before your End Sub.

Copy out the SQL statement that shows in the debug window & post it here.

That way we'll be able to see if the SQL statement you are creating has correct syntax.

Or - you can put

Code:

 
MsgBox strSQL

in the same spot in your code - and copy out the SQL statement that your MsgBox displays - and post it here.

**Robeen** · 08-23-2011, 11:22 AM

I found this page [debugging tips & tricks] helpful too.
http://support.microsoft.com/kb/142999

**simba** · 08-23-2011, 11:24 AM

This is the error message, I am getting even when I had entered the three values.

It is capturing only the last entered field. When I enter zipcode and county, Where clause is using only County.

When I use zipcod, county and type of service, where clause is using only type of service for the search term.

**Robeen** · 08-23-2011, 11:36 AM

You should be getting:

. . . "Child Development/Therapy Services/Screening programs"

You are getting single quotes around that when it needs to be double quotes.

check this page:
http://www.baldyweb.com/wherecondition.htm

for a really good example of how to pass double quotes.

Here's Paul's example - you can use the technique in how you build your SQL statement:

Code:

 
DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'" '(note, that's a double quote, single quote and double quote at the end)

I hope that fixes your problem.

**Robeen** · 08-23-2011, 11:48 AM

You are over-writing what is in your strSQLWhere variable.

At the end of each of your IF clauses:

If Len(Me.txtZipCode & vbNullString) Then . . .
If Len(Me.txtCountyName & vbNullString) Then . . .
If Len(Me.txtTypeOfService & vbNullString) Then . . .

You are overwriting the strSQLWhere variable with this:

Code:

strSQLWhere = strSQLWhere & strJoin

so your Where clause will only contain the last one that has been written.

You have to add the new clause to the existing one with an " AND " instead of overwriting.

I hope that helps. Let us know if you still have trouble.

**Robeen** · 08-23-2011, 12:03 PM

simba,

This is NOT working code but it is meant to give you an idea of what I think you need to do to get all the clauses into your strSQLWhere variable.

You might have to tweak it a little.

PLUS - I think you are not getting the correct " [double-quotes] around your variables. If I have more time, I will work on it.

Hopefully this will give you a starting point.

Code:

 
If Len(Me.txtZipCode & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
End If
End If
 
If Len(Me.txtCountyName & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = strSQLWhere & " AND " & [CountyName] Like " & Chr$(39) & "*" & Me.txtCountyName & "*" & Chr$(39)
Else
strSQLWhere = strSQLWhere & " AND " & [CountyName] = " & Chr$(39) & Me.txtCountyName & Chr$(39)
End If
End If
 
If Len(Me.txtTypeOfService & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = strSQLWhere & " AND " & [TypeOfService] Like " & Chr$(39) & "*" & Me.txtTypeOfService & "*" & Chr$(39)
Else
strSQLWhere = strSQLWhere & " AND " & [TypeOfService] = " & Chr$(39) & Me.txtTypeOfService & Chr$(39)
End If
End If
 
strSQLWhere = strSQLWhere & strJoin 'Put this after you've constructed your Where clause

Once again - I have not had time to test this but I'm putting it here so you have something to work with.

Let me know if you still need help & when I get time, I'll try getting it to actually work in Access.

All the best!

**Robeen** · 08-23-2011, 01:57 PM

Hi,

I got a little time and so I ran this quick mock-up to create a SQL string that is syntactically correct.

I have hard-coded values into variables because I didn't have time to create the Form that you have and use the Values from controls on the Form.

Still - the SQL comes out correct and I hope you can use this example to solve your problem.

Code:

 
Function CreateSQLSearchString()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String
 
'********** My Test Code
Dim strZip, strCountry, strService As String
Dim BoolLike As Boolean
strZip = "12345"
strCountry = "Tanzania"
strService = "Child Development programs"
BoolLike = False
 
'********** End - My Test Code
 
strJoin = " AND "
strSQLHead = "SELECT * FROM QryResourceContactsColumn "
 
'********** My Test Code
If Len(strZip & vbNullString) Then
If (BoolLike) Then
strSQLWhere = "WHERE Zipcode Like '" & Chr$(39) & "*" & strZip & "*" & Chr$(34)
Else
strSQLWhere = "WHERE Zipcode = " & Chr$(34) & strZip & Chr$(34)
End If
End If
 
If Len(strCountry & vbNullString) Then
If (BoolLike) Then
strSQLWhere = strSQLWhere & " AND CountyName Like " & Chr$(34) & "*" & strCountry & "*" & Chr$(34)
Else
strSQLWhere = strSQLWhere & " AND CountyName = " & Chr$(34) & strCountry & Chr$(34)
End If
End If
If Len(strService & vbNullString) Then
If (BoolLike) Then
strSQLWhere = strSQLWhere & " AND TypeOfService Like " & Chr$(34) & "*" & strService & "*" & Chr$(34)
Else
strSQLWhere = strSQLWhere & " AND TypeOfService = " & Chr$(34) & strService & Chr$(34)
End If
End If
 
'********** End - My Test Code
 
strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
 
MsgBox strSQL            
 
End Function

I have attached jpg's of the two sql strings in my MsgBox.

I hope this helps.

**simba** · 08-23-2011, 07:13 PM

Thanks Robeen for your immense help. Unfortunately, I cannot test out the code until next Wednesday, as I am outside of work.

Will let you know the results, whatever that may be.

search

Thread Tools

search

Similar Threads

Select field you want to search, select value, then Search

Search Log!!!!!!!!!?!?!?!?!?

help in search

Search in the navigation bar to search only a specific field ?

Search

Posting Permissions