Solved

Using LIKE and * for search form; type mismatch???

Posted on 2003-10-29
9
231 Views
Last Modified: 2012-05-04
I'm trying to create a search form for my database. I have one that searches successfully for exact matches, but I'd like to incorporate some wildcards. Here is part of the code:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sSQL = "SELECT Products.* FROM Products WHERE"

If Not IsNull(Me.txtProductName) Then
' Search by product name
sSQL = sSQL & " (((Products.ProductName)Like " * "&[Forms]![SearchForm]![txtProductName]&" * ")) AND"
End If

If Not IsNull(Me.txtCompanyName) Then
' Search by company name
sSQL = sSQL & " (((Products.CompanyName)=[Forms]![SearchForm]![txtCompanyName])) AND"
End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

As you can see in the "Search by product name" code, I've begun to integrate the like operator and wild cards (the second part has been left in its original, working state).  But I get an error saying there's a Type Mismatch.

Can anybody tell me what I'm doing wrong?

Thanks, Krystle
0
Comment
Question by:KrystleBlair
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:DanSivan
ID: 9645431
Krystle
put debug.print  sSQL to see the contents of sSQL after the last end if
Dan
0
 
LVL 5

Expert Comment

by:DanSivan
ID: 9645550
few more questions:
1. Do you want to search on full ProductName description or allow partially description?
2. The same for Company Name

from your code above - you allow the user to type part of the Product Name but full name for the company Name. Is that what you want?

You sSql, at the end of the 'if' statements should look like:

sSQL = "SELECT * FROM Products WHERE (Products.ProductName Like '*" &[Forms]![SearchForm]![txtProductName] &"*' ") AND (Products.CompanyName=" & [Forms]![SearchForm]![txtCompanyName] & ")"
Dan
0
 

Author Comment

by:KrystleBlair
ID: 9645769
Hi Dan,

There's a product in my database with ProductName = Everlast Surfacing. When I search for "everlast" it doesn't come up. I want it to.

I inserted Debug.Print sSQL, but I'm not quite sure how to use it to see the contents in that I keep getting a syntax error.

This is my code now:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sSQL = "SELECT Products.* FROM Products WHERE"

If Not IsNull(Me.txtProductName) Then
' Search by product name
sSQL = sSQL & " (Products.ProductName Like '*" &[Forms]![SearchForm]![txtProductName] &"*' ") AND"
End If

If Not IsNull(Me.txtCompanyName) Then
' Search by company name
sSQL = sSQL & " (((Products.CompanyName)=[Forms]![SearchForm]![txtCompanyName])) AND"
End If

Debug.Print sSQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

And yes, I would eventually like to make CompanyName use the wildcard as well, but I just want to learn how first then apply it to other search criteria.

Thanks!!

-Krystle
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:Drizzt95
ID: 9646338
What I tend to do when I 'm debugging SQL statements is to put the SQL statement in a textbox, then copy and paste the code directly into an access query.  The query will usually tell you exactly where the problem is.

E.g.

Where you have Debug.print sSQL put (I din't think this function actually did anything in access, i though it was just for VB)
text1 = sSQL
0
 
LVL 7

Expert Comment

by:donpricejr
ID: 9646400
I have not read through all of this, so if I am off base my apologies, but here is my two cents...

Like ("*" & [Parameter] & "*")

-Don
0
 
LVL 5

Accepted Solution

by:
DanSivan earned 100 total points
ID: 9647407
Krystle
1. You can see the debug.print  in the immediate window (Ctl-g)
2 Change your coe to:

 Comment from DanSivan
Date: 10/29/2003 02:59PM CST  Your Comment  

Krystle
put debug.print  sSQL to see the contents of sSQL after the last end if
Dan

 
Comment from DanSivan
Date: 10/29/2003 03:15PM CST  Your Comment  

few more questions:
1. Do you want to search on full ProductName description or allow partially description?
2. The same for Company Name

from your code above - you allow the user to type part of the Product Name but full name for the company Name. Is that what you want?

You sSql, at the end of the 'if' statements should look like:

sSQL = "SELECT * FROM Products WHERE (Products.ProductName Like '*" &[Forms]![SearchForm]![txtProductName] &"*' ") AND (Products.CompanyName=" & [Forms]![SearchForm]![txtCompanyName] & ")"
Dan

 
Comment from KrystleBlair
Date: 10/29/2003 03:39PM CST  Author Comment  

Hi Dan,

There's a product in my database with ProductName = Everlast Surfacing. When I search for "everlast" it doesn't come up. I want it to.

I inserted Debug.Print sSQL, but I'm not quite sure how to use it to see the contents in that I keep getting a syntax error.

This is my code now:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sSQL = "SELECT Products.* FROM Products WHERE"

If Not (IsNull(Me.txtProductName)  or trim(Me.txtProductName)="") Then
' Search by product name
sSQL = sSQL & " (Products.ProductName Like '*" & [Forms]![SearchForm]![txtProductName] &"*' ") AND"
End If

If Not (IsNull(Me.txtCompanyName) or trim(Me.txtCompanyName) = "" )Then
' Search by company name
sSQL = sSQL & " (Products.CompanyName='" & [Forms]![SearchForm]![txtCompanyName]) & "'"   ' <-- this is " ' "
End If

Debug.Print sSQL ' Hit Ctl - g to see the value. please post it.

Dan
 
 
0
 

Author Comment

by:KrystleBlair
ID: 9651071
Dan,

I plugged the code you gave me. The lines with the "like" operator shows up in red, and I still get a syntax error.

I tried ctrl-G and the immediate window popped up but nothing in it.

What does "trim" do? What is its role in the code?

Anyhow, here's the complete code I have at the moment:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Command5_Click()

'On Error GoTo Err_search_button_Click
Dim sSQL As String, stLen As Integer, stCaption As String, sSQLOriginal As String

sSQL = "SELECT Products.* FROM Products WHERE"

If Not (IsNull(Me.txtProductName) Or Trim(Me.txtProductName) = "") Then
' Search by product name
sSQL = sSQL & " (Products.ProductName Like '*" & [Forms]![SearchForm]![txtProductName] &"*' ") AND"
stCaption = stCaption & "Product Name = " & Me.txtProductName & " "
End If

If Not (IsNull(Me.txtCompanyName) Or Trim(Me.txtCompanyName) = "") Then
' Search by company name
sSQL = sSQL & " (Products.CompanyName='" & [Forms]![SearchForm]![txtCompanyName]) & "'"   ' <-- this is " ' "
stCaption = stCaption & "Company Name = " & Me.txtCompanyName & " "
End If

Debug.Print sSQL ' Hit Ctl - g to see the value. please post it.


If Right(sSQL, 3) = "AND" Then
stLen = Len(sSQL)
sSQL = Left(sSQL, stLen - 4)
End If

If sSQL = sSQLOriginal Then
MsgBox "Please enter search criteria", vbCritical, "Search Unsuccessful"
End
End If

DoCmd.OpenForm "ResultsForm"
Forms![ResultsForm].Form.RecordSource = sSQL
Forms![ResultsForm].Form.Caption = stCaption

Exit_search_button_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description, vbCritical, "Please report error"
'    Resume Exit_del_rec_button_Click

End Sub

Private Sub Command6_Click()

Dim cntrl As Control
For Each cntrl In Me.Controls
If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then
cntrl.Value = Null
End If
Next cntrl
DoCmd.GoToControl "txtProductName"

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-Krystle
0
 

Author Comment

by:KrystleBlair
ID: 9651720
Dan,

There was an extra "quotes" at the end of the search statements. Upon removing it the code began to work.

Now I have the issue that searching for "CompanyName" doesn't work. The table it searches (Products) has a lookup column that shows company names from a query based on another table (Companies); so because of the presence of a relationship, the chosen value gets stored as a unique ID, not as a name that can be matched with the search criteria.

Any ideas on how I can maneuver around this?

-Krystle
0
 

Author Comment

by:KrystleBlair
ID: 9651886
OK....Nevermind...I figured it out. Wouldn't have been able to do it without you, though. Thanks again for your help. I'm accepting one of your answers and just in case you're curious, here's what I came up with:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim sSQL As String, stLen As Integer, stCaption As String, sSQLOriginal As String
sSQL = "SELECT Products.* FROM Products WHERE"
stCaption = "Search Results.  "
sSQLOriginal = sSQL

If Not (IsNull(Me.txtProductName) Or Trim(Me.txtProductName) = "") Then
' Search by product name
sSQL = sSQL & " (Products.ProductName Like '*" & [Forms]![SearchForm]![txtProductName] & "*' ) AND"
stCaption = stCaption & "Product Name = " & Me.txtProductName & " "
End If

If Not (IsNull(Me.txtCompanyName) Or Trim(Me.txtCompanyName) = "") Then
' Search by company name
sSQL = sSQL & " (Products.Company Like (SELECT Companies.CompanyID FROM Companies WHERE Companies.CompanyName Like '*" & [Forms]![SearchForm]![txtCompanyName] & "*' )) AND"
stCaption = stCaption & "Company Name = " & Me.txtCompanyName & " "
End If

Debug.Print sSQL

If Right(sSQL, 3) = "AND" Then
stLen = Len(sSQL)
sSQL = Left(sSQL, stLen - 4)
End If

If sSQL = sSQLOriginal Then
MsgBox "Please enter search criteria", vbCritical, "Search Unsuccessful"
End
End If

DoCmd.OpenForm "ResultsForm"
Forms![ResultsForm].Form.RecordSource = sSQL
Forms![ResultsForm].Form.Caption = stCaption

Exit_search_button_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description, vbCritical, "Please report error"
'    Resume Exit_del_rec_button_Click
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question