Solved Like Operator in VBA

[alvingenius]

Hello
I wanna change a record source of a form using VBA.
Form Record source is SQL statement

SELECT tbl_Names.ALC_ID, tbl_Names.ALC_Number, tbl_Names.OrderID, tbl_Names.Cat1 FROM tbl_Names WHERE (((tbl_Names.ALC_Number)=[Forms]![frm_Intro]![cboSession]) AND ((tbl_Names.Cat1) Like "*" & [Forms]![frm_Names]![FilterCat1] & "*")) ORDER BY tbl_Names.OrderID, tbl_Names.Cat1;

For a specific reason, I've to use this Like Operator

I believe I can't use the same statement of the like operator in VBA with same ""or *

Like "*" & [Forms]![frm_Names]![FilterCat1] & "*"

so how to make this code work in VBA

Thanks.

Edit 1:
Fix in this post

Thanks all

 

[sonic8]

The Like operator only works with text columns. Text criteria has to be enclosed in delimiters.
So, try it this way:

       Like "'*" & [Forms]![frm_Names]![FilterCat1] & "*'"

 

[vba_php]

interesting thing here, sonic. it seems as though that's not correct. if one types this into a function:

s = "select table.field from table where table.field like " & """" & "*" & [number] & "*" & """"

it returns the right records just like it would if it was a text field. the same thing can be done with the query builder, although the query returns all requested records like VBA does, PLUS an extra record with just the value [number] in it. pretty weird stuff!

 

[Dreamweaver]

It should be like this like """ & "*" & FieldName & "*" & """"

 

[vba_php]

It should be like this like """ & FieldName & "*" & """"

are you speaking to me, Mick? if you are, there's multiple ways to do it. which is different than web stuff, as most of those use the singles (') and doubles ("), which can be substituted one for the other most of the time.

 

[Dreamweaver]

Not sure this will work but might be wrong
like " & """" & "*" & [number] & "*" & """"

 

[sonic8]

interesting thing here, sonic. it seems as though that's not correct.

It is correct.
I could have amended, though: For columns of other types, Like would be applied to the result of converting the stored data to text.
While this does "work", it kills performance, makes little logical sense, and may yield unexpected results.

 

[vba_php]

While this does "work", it kills performance, makes little logical sense, and may yield unexpected results.

and therein is the difference between the "pros" and someone like me. forever a mystery....

 

[alvingenius]

I've changed " to ' in VBA even in query criteria and it works now

Like '*' & [Forms]![frm_Names]![FilterCat1] & '*'

Thanks all