Using "Like" in Data Environment commands 1

[DTJeff]

Hi,

I hope someone can help me here as I'm pulling my hair out.

I'm trying to set up a query to an access database that returns all records where a field contains part of a specified string.

This would normally be "Like '*A*'" where A would be the word / part word, but what I want is the A to be a parameter.

Can this be done in the data environment?

Thanks.

Jeff

 

[johnwm]

Hi and welcome to the forum. May I recommend that you read faq222-2244 to get the best from the forum. For your particular question:

Set up your DataEnvironment, with a Connection and a Command object. In the command set up your query by setting the CommandType to adCmdText and putting your query in the Commands CommandText property:

e.g SELECT Add1, Add2, Add3, ClientName, Sold, SoldDate FROM tblClients WHERE (ClientName LIKE input1)


Then in code set the parameters thus:
[tt]
Dim strParam1 As String

strParam1 = "%" & Text1.text & "%"

With DataEnvironment1
If .Connection1.State <> 0 Then .Connection1.Close
DoEvents
.Connection1.Open
.Command1 (strParam1)
End With
[/tt]
Note that wildcard operator in SQL queries is '%' not '*'

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[DTJeff]

Ah!

I knew it was simple, I just couldnt work it out. I didnt think of passing the wildcard as part of the parameter!

Thanks, great help.

Jeff.

 

[VDavid]

If it help you, you should give him a star (Click in 'Mark this post as a helpful/expert post').

It is a way of letting people your appreciation for the help.

 

[rvBasic]

Is this a VB question or an Access Question? If the latter, start a new query and select the table to query.

Use the following Criteria for the Field.

LIKE [Enter Selection]

Run the query. A dialog box will show up permitting you to enter your selection criteria.

In your case enter *A*

Attention! Access still uses * as its wild card character (unless you explicitely choose ANSI-SQL92)

Of course &quot;Enter Selection&quot; can be changed for any relevant message you want to purvey to the user. Only the square brackets are essential


_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[johnwm]

Thanks for the star!

rvBasic - the clue was in the question < Can this be done in the data environment? >

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rvBasic]

Of Course! (with hindsight)

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]