How to correctly express a STRING or VARIANT in the FILTER sentence ???

[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]

 

[Douglas J Steele]

You'd best explain a little better, then, because John's code does exactly
what you asked for.

If you insist on ignoring John's advice not to use the filter if the
combobox is Null, you can use:

[Forms]![aab].Filter = " name like '" & jxsjc & "'"

or

[Forms]![aab].Filter = " name like " & Chr$(34) & jxsjc & Chr$(34)

(The latter is better, because it'll be able to handle names with
apostrophes in them, like O'Reilly)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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]