Applying a filter

  • Thread starter Thread starter John Martin
  • Start date Start date
J

John Martin

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"
 
John said:
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"
Click to expand...

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*'"
 
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"
Click to expand...

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") & " * ' "
 
Back
Top