Applying a filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Martin - 20 Jan 2005 00:50 GMT

I wish to create an input box into which the user can type all or part of a
field to open a form which will show all the fields matching the criteria.
According to "Help" a SQL expression (without the word WHERE) should do the
trick. I imagined that one of the following would work, but neither does:

DoCmd.Openform "Table Name", acViewnormal, , Field Name = "ma" & "*"

DoCmd.Openform "Table Name", acViewnormal, , Field Name LIKE "ma"

Or am I going about this the wrong way? This must be a common procedure. You
will appreciate that I am more than eligible for "gettingstarted"

Reply to this Message

Rick Brandt - 20 Jan 2005 01:06 GMT

> I wish to create an input box into which the user can type all or
> part of a field to open a form which will show all the fields
[quoted text clipped - 9 lines]
> procedure. You will appreciate that I am more than eligible for
> "gettingstarted"

If field name has a space (like your example) then it needs to be inside
brackets. Also you used a wild card with "=" and no wild card with "Like" when
it should be the other way around. Then the whole thing needs to be in quotes.
And why are you using "Table Name" with DoCmd.OpenForm?

DoCmd.OpenForm "FormName",,,"[Field Name] Like 'ma*'"

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

fredg - 20 Jan 2005 01:18 GMT

> I wish to create an input box into which the user can type all or part of a
> field to open a form which will show all the fields matching the criteria.
[quoted text clipped - 7 lines]
> Or am I going about this the wrong way? This must be a common procedure. You
> will appreciate that I am more than eligible for "gettingstarted"

The Where clause must be a string.

To hard code the OpenForm:

Note: acViewNormal is the default. No need to write it.

To filter names that equals 'ma' ...
DoCmd.Openform "Table Name", , , "[Field Name] = 'ma'"

To hard code using a wild card:
To find names that start with 'ma' ...
DoCmd.Openform "Table Name", , , "[Field Name] Like 'ma*' "

To use an Input Box:
To find names that start with whatever the user enters in the input
box....
DoCmd.OpenForm "Table Name", , , "[FieldName] Like '" &
InputBox("Enter search text") & "*'"

For clarity, the quotes are like this:
"[LastName] Like ' " & InputBox("Enter search text") & " * ' "

To find records with the entered text anywhere in the field ...
DoCmd.OpenForm "Table Name", , , "[FieldName] Like '*" &
InputBox("Enter search text") & "*'"

For clarity, the quotes are like this:
"[LastName] Like ' * " & InputBox("Enter search text") & " * ' "

Signature

Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Reply to this Message