Solved

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

Posted on 2003-10-29
9
228 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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