Building dynamic where clause with like & "*"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the number
1 in the strSearch field the search returns blank and I would expect quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
 
Try

Where = "[QuoteID] Like '" & txtToSearch & "*'"

Just as you'll do with a text field
--
Good Luck
BS"D


Barry said:
I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the number
1 in the strSearch field the search returns blank and I would expect quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
 
Dear Ofer:
If you look carefully in my description of the problem you will see that I
have tried this. Might you have any other suggestions? Should it actually
be working like that? Would you expect to see results like 1, 10, 11, 100,
101, 110 etc? Does the data need to be changed ie CInt(QuoteID)? This all
seems to work rather easily in a query grid, but in code I'm having
difficulties and don't understand why it won't translate over.

Shalom,
Barry

Ofer Cohen said:
Try

Where = "[QuoteID] Like '" & txtToSearch & "*'"

Just as you'll do with a text field
--
Good Luck
BS"D


Barry said:
I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the number
1 in the strSearch field the search returns blank and I would expect quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
Click to expand...
 
Test if the control is Null. If it is, leave the Where string blank, and
omit the WHERE clause from the SQL statement.

This style of thing:
If Not IsNull(Me.txtToSearch) Then
Select Case ...
'etc
End If
strSql = "SELECT ... FROM ..."
If Where <> vbNullString Then
strSql = strSql & " WHERE " & Where
End If

You need to do this for text fields as well. If a field is null (left blank)
it is NOT returned by
Like "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Barry said:
I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty
the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for
the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the
number
1 in the strSearch field the search returns blank and I would expect
quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
 
Hi Barry
You wrote that you tried this

Where = "[QuoteID] like " & txtToSearch & "*"

My suggestion was to add single quote before and after the value, just as
you did with the string field.
When I tried it without the single quote I got an error, as I added the
single quote it worked fine.

Where = "[QuoteID] like '" & txtToSearch & "*' "

--
Good Luck
BS"D


Barry said:
Dear Ofer:
If you look carefully in my description of the problem you will see that I
have tried this. Might you have any other suggestions? Should it actually
be working like that? Would you expect to see results like 1, 10, 11, 100,
101, 110 etc? Does the data need to be changed ie CInt(QuoteID)? This all
seems to work rather easily in a query grid, but in code I'm having
difficulties and don't understand why it won't translate over.

Shalom,
Barry

Ofer Cohen said:
Try

Where = "[QuoteID] Like '" & txtToSearch & "*'"

Just as you'll do with a text field
--
Good Luck
BS"D


Barry said:
I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the number
1 in the strSearch field the search returns blank and I would expect quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
Click to expand...
Click to expand...
 
Ofer, the single quote delimiter around a text value:
- is technically incorrect for a numeric field,
- has the problems that Barry points out (spurious matches),
- has the problems I pointed out (nulls are missed).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ofer Cohen said:
Hi Barry
You wrote that you tried this

Where = "[QuoteID] like " & txtToSearch & "*"

My suggestion was to add single quote before and after the value, just as
you did with the string field.
When I tried it without the single quote I got an error, as I added the
single quote it worked fine.

Where = "[QuoteID] like '" & txtToSearch & "*' "

--
Good Luck
BS"D


Barry said:
Dear Ofer:
If you look carefully in my description of the problem you will see that
I
have tried this. Might you have any other suggestions? Should it
actually
be working like that? Would you expect to see results like 1, 10, 11,
100,
101, 110 etc? Does the data need to be changed ie CInt(QuoteID)? This
all
seems to work rather easily in a query grid, but in code I'm having
difficulties and don't understand why it won't translate over.

Shalom,
Barry

Ofer Cohen said:
Try

Where = "[QuoteID] Like '" & txtToSearch & "*'"

Just as you'll do with a text field
--
Good Luck
BS"D


:

I am building a search form that will allow the selection of data to
search
by, ie company, QuoteNumber, QuoteDate. The string portion
(CompanyName)
allows the use of like and "*". When the field is changed and is
empty the
entire recordset is returned in the listbox. However, I cannot seem
to
repeat that functionality with the integer (QuoteID) or date
(QuoteDate)
criteria. The code I am using will follow below. The questions are
for the
Case 2 and Case 3 portions of the select case statement. I have
tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the
number
1 in the strSearch field the search returns blank and I would expect
quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID,
tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen
- is technically incorrect for a numeric field,
Click to expand...
I agree, but when I tried it in a query writing
Like 1*
The query gave me an error
Changing the criteria to
Like '1*'
Gave me the right resault, returning 1,10,1112, etc, and this is why I
suggested adding the single quote
- has the problems I pointed out (nulls are missed).
Click to expand...
I thought adding the Like '*' will return all records incase of a Null.
===============================================
English is not my first language and sometimes I have difficulties
understanding the question, and it could be that I'm on the wrong direction,
and if that the case then my appolegize to Barry for wasting his time.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ofer Cohen said:
Hi Barry
You wrote that you tried this

Where = "[QuoteID] like " & txtToSearch & "*"

My suggestion was to add single quote before and after the value, just as
you did with the string field.
When I tried it without the single quote I got an error, as I added the
single quote it worked fine.

Where = "[QuoteID] like '" & txtToSearch & "*' "

--
Good Luck
BS"D


Barry said:
Dear Ofer:
If you look carefully in my description of the problem you will see that
I
have tried this. Might you have any other suggestions? Should it
actually
be working like that? Would you expect to see results like 1, 10, 11,
100,
101, 110 etc? Does the data need to be changed ie CInt(QuoteID)? This
all
seems to work rather easily in a query grid, but in code I'm having
difficulties and don't understand why it won't translate over.

Shalom,
Barry

:

Try

Where = "[QuoteID] Like '" & txtToSearch & "*'"

Just as you'll do with a text field
--
Good Luck
BS"D


:

I am building a search form that will allow the selection of data to
search
by, ie company, QuoteNumber, QuoteDate. The string portion
(CompanyName)
allows the use of like and "*". When the field is changed and is
empty the
entire recordset is returned in the listbox. However, I cannot seem
to
repeat that functionality with the integer (QuoteID) or date
(QuoteDate)
criteria. The code I am using will follow below. The questions are
for the
Case 2 and Case 3 portions of the select case statement. I have
tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the
number
1 in the strSearch field the search returns blank and I would expect
quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID,
tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
Click to expand...
Click to expand...
 
Hey, Ofer, no apology is needed.

Your English seems good to me, so if you know another language even better,
that's brilliant. English is my first (and only real) language, but I some
of my answers are off target (usually because I don't understand the
poster's question well), and I appreciate knowing so.

And regarding this particular point, lots of people who *do* speak English
as their first language don't understand that using:
Like "*"
does not return all records. The results are equivalent to:
Is Not Null
 
Thanks Allen
My first language is Hebrew, but leaving in Sydney,Australia for few years,
and been meried to an Australian from Canberra that refuse to comunicate with
me in Hebrew made me learn another language, which I enjoy.

You right, I forgot about the Null metter with Like, I even remember
answering few posts on the subject.
So, thank you for reminder, and the info.
 
Allen:
Many thanks to you and Ofer for the answers and all the banter in between.
I did not realize that using the "*" is akin to is not null. I will endeavor
to incorporate these ideas into my code and I am certain that my application
will be the better. Again many thanks,

Barry

Allen Browne said:
Test if the control is Null. If it is, leave the Where string blank, and
omit the WHERE clause from the SQL statement.

This style of thing:
If Not IsNull(Me.txtToSearch) Then
Select Case ...
'etc
End If
strSql = "SELECT ... FROM ..."
If Where <> vbNullString Then
strSql = strSql & " WHERE " & Where
End If

You need to do this for text fields as well. If a field is null (left blank)
it is NOT returned by
Like "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Barry said:
I am building a search form that will allow the selection of data to search
by, ie company, QuoteNumber, QuoteDate. The string portion (CompanyName)
allows the use of like and "*". When the field is changed and is empty
the
entire recordset is returned in the listbox. However, I cannot seem to
repeat that functionality with the integer (QuoteID) or date (QuoteDate)
criteria. The code I am using will follow below. The questions are for
the
Case 2 and Case 3 portions of the select case statement. I have tried to
use: Where = "[QuoteID] like " & txtToSearch & "*" but if you put the
number
1 in the strSearch field the search returns blank and I would expect
quotes
numbered 1, 10, 11, 12, 100, 101 etc.

Private Sub strSearch_Change()

'Setup variables
Dim strSQL As String
Dim Where As String
Dim SearchBy As Integer
Dim txtToSearch As Variant

'Combo box for field to search. String to search
SearchBy = Me!cboSearchBy
txtToSearch = Me!strSearch.Text

'Setup the Where clause
Select Case (SearchBy)
Case 1
Where = "[CompanyName] Like '" & txtToSearch & "*" & "'"
Case 2
Where = "[QuoteID] Like " & txtToSearch
Case 3
Where = "[QuoteDate] Like #" & txtToSearch & "#"
End Select

'Construct SQL to be used as listbox RowSource
strSQL = "Select tblQuotesBattery.QuoteID, tblCustomers.CompanyName,
tblQuotesBattery.QuoteDate, tblQuotesBattery.CustomerID" & _
" FROM tblCustomers INNER JOIN tblQuotesBattery" & _
" ON tblCustomers.CustomerID = tblQuotesBattery.CustomerID" & _
" Where " & Where

'Set the RowSource
lstQuoteBattery.RowSource = strSQL
lstQuoteBattery.Requery
Me!strSearch.SetFocus

End Sub

Your comments are greatly appreciated.

Thanks,
Barry
Click to expand...
Click to expand...
 
Ofer:
Many thanks to you and Allen. The discussion between professionals
contributes greatly to my learning experience. I will be using the ideas
from these posts to make my application better.

Shalom Alechem,
Barry
 
Back
Top