Like "MU*" OR Like "MS*"

R

Roger Collette

Hello;

I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.


Combo28 (Coulmn (1) is where hte criteria is



List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'

12 Like "??T*"

13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors



when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
Private Sub Combo28_AfterUpdate()

Dim strSelected As String



'strSelected = "select * from MYTABLE_COMPONENT where [CMPNT_NAME]" &
Me![Combo28].Column(1)

strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)

Me!lstInstrIssue.RowSource = strSelected

Me.Combo6 = ""

Me!cmdSelectIssue.Enabled = True

Me!cmdclearall.Enabled = True



lstInstrIssue.Requery



End Sub
 
D

Douglas J. Steele

You can't shortcircuit the WHERE clause like that in SQL. If you're building
a query through the graphical query interface and you type Like "MU*" OR
Like "MS*" in the Criteria cell of the grid then go look at the SQL that's
generated, you'll find it's something like

WHERE (((Query2.[CMPNT_NAME]) Like "MU*" Or (Query2.[CMPNT_NAME]) Like
"MS*"))
 
M

Marshall Barton

Roger said:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
 
J

John Spencer

Or you could try

Like "M[SU]*"




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Marshall said:
Roger said:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
 
T

tony Jacobs

Like "M*" works

Like 'M*' works also. Note that the code has the varaible [CMPNT_NAME]
already in it. So I can't say CMPNT_NAME = [CMPNT_NAME] Like "HV*"

"HV*" also works




John Spencer said:
Or you could try

Like "M[SU]*"




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Marshall said:
Roger said:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
Click to expand...
 
D

Douglas J. Steele

tony Jacobs said:
Like 'M*' works also. Note that the code has the varaible [CMPNT_NAME]
already in it. So I can't say CMPNT_NAME = [CMPNT_NAME] Like "HV*"
Click to expand...

You'd need to change your code to

strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM [Query2] where " & Me![Combo28].Column(1)

if you wanted to use Marsh's suggestion. Of course, John's suggestion (of
using Like "M[SU]*" instead of Like "MU*" OR Like "MS*") will work for that
specific case.
 
T

tony Jacobs

Marshall;
The condition or the value of the Combobox changes as it is what sets the
criteria. So I can not hard code the criteria .

Thank you


Marshall Barton said:
Roger said:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
 
T

tony Jacobs

Doug;

So you are saying I should remove the varaible

From this statement in the end of SQL : where " & Me![Combo28].Column(1) ;
i.e use it with no [CMPNT_NAME] ??

If that is the case how will it determine what I am filtering on ? I am
sorry I am not sure I understand.

Thanks in Advance

Douglas J. Steele said:
tony Jacobs said:
Like 'M*' works also. Note that the code has the varaible [CMPNT_NAME]
already in it. So I can't say CMPNT_NAME = [CMPNT_NAME] Like "HV*"
Click to expand...

You'd need to change your code to

strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM [Query2] where " & Me![Combo28].Column(1)

if you wanted to use Marsh's suggestion. Of course, John's suggestion (of
using Like "M[SU]*" instead of Like "MU*" OR Like "MS*") will work for that
specific case.
Click to expand...
 
M

Marshall Barton

I do not understand what you think is being "hard coded".
Your table has the criteria, it is just insufficient for the
compound condition. If you carefully look at what I
suggested, you'll see that the critical point is that the
field name must be used with each comparison. That is, you
must use:

[fld] Like "pattern1*" OR [fld] Like "pattern2*"

instead of the shorthand stuff you might enter in the query
design grid. In other words, this will not work:
[fld] Like "pattern1*" OR Like "pattern2*"
because the second Like has no left hand operand.

If anything, my suggestion is less "hard coded" than what
you were using. You had the field name in your VBA code
whereas I put it in the table so the same list could allow
filtering on different and/or multiple fields.
--
Marsh
MVP [MS Access]


tony said:
The condition or the value of the Combobox changes as it is what sets the
criteria. So I can not hard code the criteria .


Roger said:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated in the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Re-read Marsh's suggestion.

He's suggesting that you put the field name what you're storing.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tony Jacobs said:
Doug;

So you are saying I should remove the varaible

From this statement in the end of SQL : where " & Me![Combo28].Column(1)
;
i.e use it with no [CMPNT_NAME] ??

If that is the case how will it determine what I am filtering on ? I am
sorry I am not sure I understand.

Thanks in Advance

Douglas J. Steele said:
tony Jacobs said:
Like 'M*' works also. Note that the code has the varaible [CMPNT_NAME]
already in it. So I can't say CMPNT_NAME = [CMPNT_NAME] Like "HV*"
Click to expand...

You'd need to change your code to

strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM [Query2] where " & Me![Combo28].Column(1)

if you wanted to use Marsh's suggestion. Of course, John's suggestion (of
using Like "M[SU]*" instead of Like "MU*" OR Like "MS*") will work for that
specific case.
Click to expand...
Click to expand...
 
T

tony Jacobs

Ahhh. Okay. I now see what you mean. But since CMPNT_Name is the only thing
I am after and thr user may add more criteria (Where he/she have little
access Knowledge), that's why I opted to build a table with the different
iterations. You suggested to take the field name from the code and put it in
the criteria table I like that too.

It looks like the "M[SU]*" it works just fine.

Thanks for your time , Marsh and Doug. and everyone who answered :=)

Now if you can give me a hand with my Case statement, I'll be a very happy
camper.

Thanks all.





Marshall Barton said:
I do not understand what you think is being "hard coded".
Your table has the criteria, it is just insufficient for the
compound condition. If you carefully look at what I
suggested, you'll see that the critical point is that the
field name must be used with each comparison. That is, you
must use:

[fld] Like "pattern1*" OR [fld] Like "pattern2*"

instead of the shorthand stuff you might enter in the query
design grid. In other words, this will not work:
[fld] Like "pattern1*" OR Like "pattern2*"
because the second Like has no left hand operand.

If anything, my suggestion is less "hard coded" than what
you were using. You had the field name in your VBA code
whereas I put it in the table so the same list could allow
filtering on different and/or multiple fields.
--
Marsh
MVP [MS Access]


tony said:
The condition or the value of the Combobox changes as it is what sets the
criteria. So I can not hard code the criteria .


Roger Collette wrote:
I have a combobox that looks at a table which sets the criteria of a field .
Based on the criteria ; the list box in the same form gets populated
Click to expand...
in
the
form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*"
14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME] Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
Click to expand...
Click to expand...
 
D

DbHobbyist

All the other replies got it wrong and got too complicated. The problem lies
with the '*' character. You should be using '%' for databases outside of
Access.

e.g. where field_name like 'MU%'

Hope that helps...
 
D

Douglas J. Steele

* is the correct wildcard character in Access, unless you're using ADO.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


DbHobbyist said:
All the other replies got it wrong and got too complicated. The problem
lies
with the '*' character. You should be using '%' for databases outside of
Access.

e.g. where field_name like 'MU%'

Hope that helps...

Roger Collette said:
Hello;

I have a combobox that looks at a table which sets the criteria of a
field .
Based on the criteria ; the list box in the same form gets populated in
the
form.


Combo28 (Coulmn (1) is where hte criteria is



List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'

12 Like "??T*"

13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors



when I enter Like "MU*" it works, Like "MS*" also works on different
lines
in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also tried
Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
Private Sub Combo28_AfterUpdate()

Dim strSelected As String



'strSelected = "select * from MYTABLE_COMPONENT where [CMPNT_NAME]" &
Me![Combo28].Column(1)

strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM
[Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)

Me!lstInstrIssue.RowSource = strSelected

Me.Combo6 = ""

Me!cmdSelectIssue.Enabled = True

Me!cmdclearall.Enabled = True



lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
 
R

Rocky5

Why does this take a long time to execute? It works though.


John Spencer said:
Or you could try

Like "M[SU]*"




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Marshall said:
Roger said:
I have a combobox that looks at a table which sets the criteria of a
field . Based on the criteria ; the list box in the same form gets
populated in the form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different
lines in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also
tried Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM [Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)
Click to expand...


You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*" 14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME]
Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
Click to expand...
 
J

John Spencer

IS the field cmpnt_name indexed? If not you could be doing a full table
scan and if there are a lot of records, that will take time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Why does this take a long time to execute? It works though.


John Spencer said:
Or you could try

Like "M[SU]*"




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Marshall said:
Roger Collette wrote:
I have a combobox that looks at a table which sets the criteria of a
field . Based on the criteria ; the list box in the same form gets
populated in the form.

Combo28 (Coulmn (1) is where hte criteria is

List name is : lstInstrIssue (Shows results)

Table for criteria is criteria2:
criteria2 Product ID Product Name Product Description
1 Like 'HV*' All Automated Valves
2 Like 'MU*' All Motors
3 Like 'AI*' All Analog Inputs
4 Like 'AO*' All Analog Outputs
9 Like 'SI*' All Switches
10 Like 'T*' ProfiBus Transmitter
11 Like 'MS*'
12 Like "??T*"
13 Like "HV*" Test all automated Valves
14 Like "MU*" OR Like "MS*" Test All Motors

when I enter Like "MU*" it works, Like "MS*" also works on different
lines in the combo box. However if I use

Like "MU*" OR Like "MS*" on the same line it stops working. I also
tried Like 'MU*' . What am I doing wrong.

The back end tables are on SQL server.

Here is the Code;
strSelected = "SELECT [Query2].CMPNT_ID,[Query2].CMPNT_NAME,
[Query2].CMPNT_NUM,[Query2].SPEC_CMPNT_TYPE,[Query2].SPEC_CMPNT_FUNC
FROM [Query2] where [CMPNT_NAME]" & Me![Combo28].Column(1)

You could change what's in the table and code:

... . . .
13 [CMPNT_NAME] Like "HV*" 14 [CMPNT_NAME] Like "MU*" OR [CMPNT_NAME]
Like "MS*"

" . . . Where " & Me![Combo28].Column(1)
Click to expand...
Click to expand...
Click to expand...
 
Top