using Like (or alternative) in SQL via VBA

[bd528]

Hi all,

I'd like to use the equivalent of Like in an SQL query via VBA.

The code below finds the exact value :-

strSQL = "SELECT tblBrokerages.ID, tblBrokerages.Brokerage_Name AS [Brokerage Name], tblBrokerages.Broker_Code AS [Broker Code] FROM tblBrokerages WHERE tblBrokerages.Brokerage_Name = '" & Me.Text76.Value & "';"

I believe I may need to use '%' or '*', but I'm struggling with the syntax...

Thanks in advance.

 

[Ranman256]

you dont have to use vba, you can put it in a query.

if using vba for access
WHERE tblBrokerages.Brokerage_Name like '*" & Me.Text76 & "*'"

 

[bd528]

you dont have to use vba, you can put it in a query.

if using vba for access
WHERE tblBrokerages.Brokerage_Name like '*" & Me.Text76 & "*'"

It does have to be VBA unfortunately. My whole search form is built around it.

 

[Minty]

Be aware that placing the wildcards at the beginning of a search string renders the index on that field to be virtually worthless during the search.
This can make searches on larger datasets become VERY slow.

The general rule is only wildcard at the end if you can get way with it, or better still limit the search to an exact match provided by a combo who's recordsource is already restricted to valid search strings.
So for example, if you are looking for company names with open orders rather than listing every company on your books, only let them search for company's which have current orders.

 

[bd528]

Be aware that placing the wildcards at the beginning of a search string renders the index on that field to be virtually worthless during the search.
This can make searches on larger datasets become VERY slow.

The general rule is only wildcard at the end if you can get way with it, or better still limit the search to an exact match provided by a combo who's recordsource is already restricted to valid search strings.
So for example, if you are looking for company names with open orders rather than listing every company on your books, only let them search for company's which have current orders.

I'm more than happy to have the wildcard return only "begins with" results, if someone could shoe me the syntax.

 

[Minty]

Just remove the wildcard (*) from the beginning e.g.

WHERE tblBrokerages.Brokerage_Name like '" & Me.Text76 & "*'"

So if Text76 was Smith you criteria would be

WHERE tblBrokerages.Brokerage_Name LIKE 'Smith*'

Where the * is the wildcard.

 

[bd528]

Thanks Minty, that's exactly what I needed.