Filter records on a form

  • 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 where, but neither does:

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

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

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"
 
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 where, but neither does:

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

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

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...

You received 2 replies on your previous post on the 19th.
 
Sorry to have post more than once. For some reason, I could not see my message
and thought that it had not arrived. I still cannot see it, despite having
searched diligently and made sure that I am listing both read and unread. I am
desperate to see the replies. Can they be added to this message, which I can
see?
 
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 where, but neither does:

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

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

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...

An Input Box is actually not all that convenient. You might find it
easier to use a small unbound form (I'll call it frmCrit) with a
textbox txtCriterion. Put a command button on the form to open the
other form. Surely you don't have a *form* named Table Name I hope -
but if you do it would be

Dim strSQL As String
strSQL = "[Field Name] LIKE " & [Forms]![frmCrit]![txtCriterion] & "*"
DoCmd.OpenForm "[Table Name]", acViewNormal, strSQL

The key points are:

- If you have names of forms or fields or controls which contain
blanks or other special characters, you MUST enclose the name in
square brackets. If you have a field called "Field Name", and don't
use brackets, the VBA compiler will see "Field" as one word, and
"Name" as a different word, and get very confused.
- The WhereCondition argument must be a text string - either a string
variable as in my example, or it could be a string constant such as

"[FieldName] = 312"

in quotes to tell Access that it's a string.


John W. Vinson[MVP]
 
Sorry to have post more than once. For some reason, I could not see my message
and thought that it had not arrived. I still cannot see it, despite having
searched diligently and made sure that I am listing both read and unread. I am
desperate to see the replies. Can they be added to this message, which I can
see?
Click to expand...

Reply copied from my previous post.


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