Solved Like Operator in VBA (1 Viewer)

alvingenius

IT Specialist
Local time
Tomorrow, 01:59
Joined
Jul 10, 2016
Messages
169
Hello
I wanna change a record source of a form using VBA.
Form Record source is SQL statement
SQL:
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 *

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

so how to make this code work in VBA

Thanks.

Edit 1:
Fix in this post

Thanks all
 
Last edited:

sonic8

AWF VIP
Local time
Tomorrow, 00:59
Joined
Oct 27, 2015
Messages
998
The Like operator only works with text columns. Text criteria has to be enclosed in delimiters.
So, try it this way:
SQL:
       Like "'*" & [Forms]![frm_Names]![FilterCat1] & "*'"
 

vba_php

Forum Troll
Local time
Today, 18:59
Joined
Oct 6, 2019
Messages
2,884
interesting thing here, sonic. it seems as though that's not correct. if one types this into a function:
Code:
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

Well-known member
Local time
Today, 23:59
Joined
Nov 28, 2005
Messages
2,466
It should be like this like """ & "*" & FieldName & "*" & """"
 

vba_php

Forum Troll
Local time
Today, 18:59
Joined
Oct 6, 2019
Messages
2,884
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

Well-known member
Local time
Today, 23:59
Joined
Nov 28, 2005
Messages
2,466
Not sure this will work but might be wrong
like " & """" & "*" & [number] & "*" & """"
 

sonic8

AWF VIP
Local time
Tomorrow, 00:59
Joined
Oct 27, 2015
Messages
998
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

Forum Troll
Local time
Today, 18:59
Joined
Oct 6, 2019
Messages
2,884
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

IT Specialist
Local time
Tomorrow, 01:59
Joined
Jul 10, 2016
Messages
169
I've changed " to ' in VBA even in query criteria and it works now
SQL:
Like '*' & [Forms]![frm_Names]![FilterCat1] & '*'

Thanks all
 

Users who are viewing this thread

Top Bottom