Problem using wild card characters

[kriehn16]

I am making a program that runs out of Outlook on the front end and uses an Access database as the backend.
I have a field in the database that is a test number. It is a 8 digit number set up in the following format ###-###-##
The user will know the first 3 digits and the last 2 digits so I want to search knowing these five digits and allowing
the middle three digits to be anything, however, I have tried using *, #, and % as wildcards but it always
returns nothing. My code is below. Any help would be much appreciated.

Private Sub CommandButton1_Click()

Dim count As Integer
Dim stSQL As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open stdbName

If TextBox1.Value = "" Then
MsgBox "Please Input a Product Number"
Else
stSQL = "SELECT * FROM [Report Info] WHERE [Test Number] = '" & TextBox1.Value & "-%%%-99'"
rs.CursorLocation = adUseClient
rs.Open stSQL, cn
count = rs.RecordCount
rs.Close
End If
End Sub

-joker16

 

[PHV]

Use the LIKE operator !

stSQL = "SELECT * FROM [Report Info] WHERE [Test Number] LIKE '" & TextBox1.Value & "-%-99'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Access using the like operator uses two wildcards: * and ?.

* - Any number of characters
? - Exactly one Character

stSQL = "SELECT * FROM [Report Info] WHERE [Test Number] [red]LIKE[/red] '" & TextBox1.Value & "-[red]???[/red]-99'"

 

[PHV]

the like operator uses two wildcards: * and ?
The OP uses ADO and thus needs the standard wildcards: % and _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

PHV,

When I saw I cross posted I wondered about the ANSI-92 wildcards and ADO...

I missed picking that one up. I've only really used ADO with SQL Server not Jet.

joker16,

The % is the one that is any number of characters.

 

[kriehn16]

lameid and PHV,

Thank you for your help. I put LIKE in place of the equal sign and changed the %s to _s and it works fine now.

Thank you,

joker16