Solved

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

Posted on 2003-10-29
9
232 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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