SQL queries

Here is my issue, I am referencing an SQL query through a command in the dataenvironment. My question is, where and how do I use a wildcard to return values? For example, I am looking for all companies in a table that begin with "Her" here is the command code I am using but I have to enter the exact name of the company to find it...ie. "Heritage Enterprises" - where txtCompany is the variable I am using.

Private Sub cmdFilter_Click()
' run the query, passing the expected parameters.
DataEnvironment1.SearchCompany txtCompany, txtLocation, txtConfname1

' Ensure the grid is bound to the DataEnvironment
Set DataGrid1.DataSource = DataEnvironment1
DataGrid1.DataMember = "SearchCompany"
End Sub

any ideas anyone? is it something I put in the code here or do I do it in the SQL query???

help! I'm really new at this all, sorry!

Actually, here is the query(command), written in SQL.. that is being called in VB as "SearchCompany

SELECT entered, mrocomid, compwksid, company, random,
location, add1, add2, city, state, zip, confname1, conlname1,
conphone1, conext1, conemail1
FROM company
WHERE (company = ?) OR
(location = ?) OR
(confname1 = ?)
ORDER BY company, location

I am using a variable in VB for each "?" for users to search for matching companies. The "variables are the txtCompany, txtLocation, and txtConfname1 you see in my vb code. The problem is I need the girls here to type into a text box "Her" and have it return all the companies that start with HER...... They don't have access to the actual query, only the form that is referencing it....

try

hmm, no luck. the query doesn't return anything

K, I figured it out....


I use LIKE ?

then in my txtCompany box I enter for instance Her%

that returns all companies beginning with Her. Now I jsut have to figure out a way to have it automatically enter the % sign after an entry into a text box - trying to simplify for the users...

thanks again for the input!

Kristin,

I am writing a progran to allow many similar options to users and thought you might be interseted in how I do it.

My approach to wildcards is to use one at the beginning and end of the txtCompany.Text variable. These can be turned on or off by the user.

This approach allows the following options:
1. When the front wildcard is off then it means that SQLfield must start with txtCompany.Text
2. When the Back wildcard is off then it means that SQLfield must end with txtCompany.Text
3. When Both are Off then SQLField must be equal to txtCompany.Text
4. When Both are on then SQLField must contain txtCompany.Text

I place a small CommandButton on either side of the txtCompany box . Each CommandButton (cmdBtn1 & cmdBtn2) toggles a string variable that is either NULL or equal to the SQL wildcard symbol (%). I also use a CommandButton (cmdFind) to activate the search. (See an actual Form from one of my programs in the attachment.) Note: The Combo1 box in the example is my SqlField variable in this program.

Code Follows

'************************************************* **************
SUB cmdBtn1_Click()
'This subroutine toggles the front wildcard Status On and Off as the button is pressed.
' (Actually, I use an asterisk (*) to indicate that the wildcard is "On"
' and I use a Blank faced button to indicate that the wildcard is "Off")
If Variable1="" then
Variable1="%"
cmdBtn1.Caption = "On"
Else
Variable1="" then
cmdBtn1.Caption = "Off"
End If
END SUB
'************************************************* **************
SUB cmdBtn2_Click()
'This subroutine toggles the back Wildcard status On and Off as the button is pressed.
' (Actually, I use an asterisk (*) to indicate that the wildcard is "On"
' and I use a Blank faced button to indicate that the wildcard is "Off")
If Variable2="" then
Variable2="%"
cmdBtn2.Caption = "On"
Else
Variable2="" then
cmdBtn2.Caption = "Off"
End If
END SUB
'************************************************* **************
SUB cmdFind_Click()
'This routine performs the search based on the wildcard/txtCompany inputs.

'Combine the strings into one string that is used in the SqlQuery
sqlQuery= "Select * from sqlTable where sqlField like '" & Variable1 & txtCompany.Text & Variable2 & "' "

'Execute the SqlQuery using the ADODC
With ADODC1
.RecordSource = sqlQuery
.Refresh
End With

'Update and refresh the DataGrid
With DataGrid1
.ReBind
.Refresh
End With
END SUB
'************************************************* **************

Hope this helps,
Hunter

Excellent idea! I am definitely going to use this

thanks!
Kristin