Building dynamic where clause with like & "*"

B

Barry

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
 
O

Ofer Cohen

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...
 
B

Barry

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...
 
A

Allen Browne

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...
 
O

Ofer Cohen

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...
 
A

Allen Browne

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...
 
O

Ofer Cohen

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...
 
A

Allen Browne

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
 
O

Ofer Cohen

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.
 
B

Barry

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...
 
B

Barry

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Insert Into with Where Clause 13
SQL with IIF 5
Add where clause to querydef? 1
Wildcard question 2
build the where clause... 5
Using Like operator in VBA 6
help with a VBA 4
WHERE clause in query 4

Top