Solved

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

Posted on 2003-10-29
9
223 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
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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now