What's wrong with this FILTER vba

[Martin]

In a form, I place a combo (combo98) to let the user select one of the name,
and then click a button to show a form only contain this name's record. Yet,
it can't work well. The button's VBA (part of it) is as below. What's wrong
? Thanks!


dim jxsjc as string

If IsNull([Forms]![eff]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![eff]![Combo98]
End If

.......

DoCmd.OpenForm "aab", acNormal, acEdit

[Forms]![aab].Filter = " name like jxsjc"
[Forms]![aab].FilterOn = True

 

[John Vinson]

In a form, I place a combo (combo98) to let the user select one of the name,
and then click a button to show a form only contain this name's record. Yet,
it can't work well. The button's VBA (part of it) is as below. What's wrong
? Thanks!


dim jxsjc as string

If IsNull([Forms]![eff]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![eff]![Combo98]
End If

......

DoCmd.OpenForm "aab", acNormal, acEdit

[Forms]![aab].Filter = " name like jxsjc"
[Forms]![aab].FilterOn = True

Several things. The most critical is that you're searching for

name like jxsjc

and there's probably nothing in your table containing jxsjc in the
field - you're searching for the NAME of the variable rather than its
VALUE!

For another, you're using name as the name of a field. "name" is a
reserved word; Tables have a Name proeperty, as do Fields, Forms,
Controls, and so on. Access *WILL* get confused. If you must use Name
as the Name of your Name field, always put it in [brackets].

Thirdly, if you want to leave the form unfiltered (find all values of
[Name]) if the combo is blank, then it's better just to turn off the
filter altogether. And if you want it to find the exact name, you
don't need the LIKE operator; = is faster.

Fourthly, if you're searching for a value of a Text field, it must be
delimited with either ' or " quotemarks.

Try:

If IsNull(Me![Combo98]) Then ' assuming the code is on the same form
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "[Name] = '" & Me![Combo98] & "'"
End If


John W. Vinson[MVP]

 

[Martin]

No No No! Your VBA is not fit for me.

Actually, my question's key problem is how can I put a VARIANT come into
the filter using.

for example, jxsjc is a variant or string which is got by the combo,
thus, I want to make a VBA as: [Forms]![aab].Filter = " name111 like
jxsjc"

.... so , the point of the problem is : How can a variant or string go into a
.. filter=" " VBA sentence ???

In a form, I place a combo (combo98) to let the user select one of the
name,
and then click a button to show a form only contain this name's record.
Yet,
it can't work well. The button's VBA (part of it) is as below. What's
wrong
? Thanks!


dim jxsjc as string

If IsNull([Forms]![eff]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![eff]![Combo98]
End If

......

DoCmd.OpenForm "aab", acNormal, acEdit

[Forms]![aab].Filter = " name like jxsjc"
[Forms]![aab].FilterOn = True

Several things. The most critical is that you're searching for

name like jxsjc

and there's probably nothing in your table containing jxsjc in the
field - you're searching for the NAME of the variable rather than its
VALUE!

For another, you're using name as the name of a field. "name" is a
reserved word; Tables have a Name proeperty, as do Fields, Forms,
Controls, and so on. Access *WILL* get confused. If you must use Name
as the Name of your Name field, always put it in [brackets].

Thirdly, if you want to leave the form unfiltered (find all values of
[Name]) if the combo is blank, then it's better just to turn off the
filter altogether. And if you want it to find the exact name, you
don't need the LIKE operator; = is faster.

Fourthly, if you're searching for a value of a Text field, it must be
delimited with either ' or " quotemarks.

Try:

If IsNull(Me![Combo98]) Then ' assuming the code is on the same form
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "[Name] = '" & Me![Combo98] & "'"
End If


John W. Vinson[MVP]

 

[John Vinson]

for example, jxsjc is a variant or string which is got by the combo,
thus, I want to make a VBA as: [Forms]![aab].Filter = " name111 like
jxsjc"

... so , the point of the problem is : How can a variant or string go into a
. filter=" " VBA sentence ???

By concatenating its value using the & operator:

[Forms]![aab].Filter = "[Name111] LIKE " & jxsjc

That's the literal answer to your question. It will give you what you
asked for, but NOT what you actually want, since the Filter property
requires that Text fields be delimited with either ' or ". Instead,
use

[Forms]![aab].Filter = "[Name111] LIKE " & Chr(34) & jxsjc & Chr(34)

so that the Filter property will be set to

[Name111] LIKE "O'Brien"


John W. Vinson[MVP]