Filter Syntax Problem

[Guest]

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands
that they be there, but they seem to be preventing the filter from working.
When I filter by form the code is there with the single quotes at the
beginning and end and I get zero results. When I remove the single quotes and
apply the filter, it works perfectly. How do I write the code in VB so the
filter works?
..Filter = "[LastName] = 'Like """ & Left([TargetName], 3) & "*""'"
result is
[LastName] = 'Like "Smi*"' which doesn't want to work,
however,
Like "Smi*" gets me everyone whose last name begins with Smi, which is what
I want. Can anyone help me with the VB syntax for pushing a like filter into
a form?
Thanks
Michael

 

[Marshall Barton]

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands
that they be there, but they seem to be preventing the filter from working.
When I filter by form the code is there with the single quotes at the
beginning and end and I get zero results. When I remove the single quotes and
apply the filter, it works perfectly. How do I write the code in VB so the
filter works?
.Filter = "[LastName] = 'Like """ & Left([TargetName], 3) & "*""'"
result is
[LastName] = 'Like "Smi*"' which doesn't want to work,
however,
Like "Smi*" gets me everyone whose last name begins with Smi, which is what
I want. Can anyone help me with the VB syntax for pushing a like filter into
a form?

That quote is inappropriate and incorrect. The double
quotes you have around the pattern operand are ok:

..Filter = "LastName = Like """ & Left(TargetName, 3) & "*"""

Or, if you prefer, you can use the single quote this way:

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "

 

[Guest]

Marchall, I am sorry, but neither suggestion worked. The error message for
both was Error 2448 - you can't assign a value to this object. When I moused
over the line the value for your first option was: .filter = "LastName =
like "Smi*"" and the second option produced .filter = "LastName = like
'Smi*'". Thanks for your help and if you have any other suggestions I will be
happy to try them.
Thanks
Michael

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands
that they be there, but they seem to be preventing the filter from working.
When I filter by form the code is there with the single quotes at the
beginning and end and I get zero results. When I remove the single quotes and
apply the filter, it works perfectly. How do I write the code in VB so the
filter works?
.Filter = "[LastName] = 'Like """ & Left([TargetName], 3) & "*""'"
result is
[LastName] = 'Like "Smi*"' which doesn't want to work,
however,
Like "Smi*" gets me everyone whose last name begins with Smi, which is what
I want. Can anyone help me with the VB syntax for pushing a like filter into
a form?

That quote is inappropriate and incorrect. The double
quotes you have around the pattern operand are ok:

..Filter = "LastName = Like """ & Left(TargetName, 3) & "*"""

Or, if you prefer, you can use the single quote this way:

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "

 

[AccessVandal via AccessMonster.com]

Just remove the " = " .

It should be something like

"LastName like 'Smi*'"

Marchall, I am sorry, but neither suggestion worked. The error message for
both was Error 2448 - you can't assign a value to this object. When I moused
over the line the value for your first option was: .filter = "LastName =
like "Smi*"" and the second option produced .filter = "LastName = like
'Smi*'". Thanks for your help and if you have any other suggestions I will be
happy to try them.
Thanks
Michael
[quoted text clipped - 19 lines]

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "

 

[Marshall Barton]

Good catch Michael. I was so focused on the quotes that I
missed the extraneous = sign.
--
Marsh
MVP [MS Access]

Just remove the " = " .

It should be something like

"LastName like 'Smi*'"

Marchall, I am sorry, but neither suggestion worked. The error message for
both was Error 2448 - you can't assign a value to this object. When I moused
over the line the value for your first option was: .filter = "LastName =
like "Smi*"" and the second option produced .filter = "LastName = like
'Smi*'". Thanks for your help and if you have any other suggestions I will be
happy to try them.
Thanks
Michael

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands

[quoted text clipped - 19 lines]

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "

 

[Guest]

That worked, that worked, that worked!!! Thank you both. Marshall I am
sorry I focused you on the quotes, next time I will just describe the problem
instead of trying to direct the solution toward what I think will solve the
problem.
Thanks
Michael

Good catch Michael. I was so focused on the quotes that I
missed the extraneous = sign.
--
Marsh
MVP [MS Access]

Just remove the " = " .

It should be something like

"LastName like 'Smi*'"

Marchall, I am sorry, but neither suggestion worked. The error message for
both was Error 2448 - you can't assign a value to this object. When I moused
over the line the value for your first option was: .filter = "LastName =
like "Smi*"" and the second option produced .filter = "LastName = like
'Smi*'". Thanks for your help and if you have any other suggestions I will be
happy to try them.
Thanks
Michael

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands
[quoted text clipped - 19 lines]

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "

 

[Marshall Barton]

I'm glad you got it working, but there's no need for you to
apologize, I'm the one that had tunnel vision.
--
Marsh
MVP [MS Access]

That worked, that worked, that worked!!! Thank you both. Marshall I am
sorry I focused you on the quotes, next time I will just describe the problem
instead of trying to direct the solution toward what I think will solve the
problem.

Good catch Michael. I was so focused on the quotes that I
missed the extraneous = sign.

Just remove the " = " .

It should be something like

"LastName like 'Smi*'"

apollo8359 wrote:
Marchall, I am sorry, but neither suggestion worked. The error message for
both was Error 2448 - you can't assign a value to this object. When I moused
over the line the value for your first option was: .filter = "LastName =
like "Smi*"" and the second option produced .filter = "LastName = like
'Smi*'". Thanks for your help and if you have any other suggestions I will be
happy to try them.
Thanks
Michael

I am trying to apply a filter but the VB syntax keeps getting in the way. The
problem is the single quotes before the word like and at the end. VB demands
[quoted text clipped - 19 lines]

..Filter = "LastName = Like '" & Left(TargetName, 3) & "*' "