Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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

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
KrystleBlair
Asked:
KrystleBlair
1 Solution
 
DanSivanCommented:
Krystle
put debug.print  sSQL to see the contents of sSQL after the last end if
Dan
0
 
DanSivanCommented:
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
 
KrystleBlairAuthor Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Drizzt95Commented:
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
 
donpricejrCommented:
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
 
DanSivanCommented:
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
 
KrystleBlairAuthor Commented:
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
 
KrystleBlairAuthor Commented:
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
 
KrystleBlairAuthor Commented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now