Filter multiple results by user input

I have created an append query that pulls information from one
database, and will append the selected information into a new table.
The fields are setup like 'number' 'category' 'code' 'shares' and
'dollars'. Using the "backend" of this table I can filter the numbers
by right clicking and using Filter For: then enter 1 or 2 or 3 and
this will return results for all information that has 1 or 2 or 3 as a
number.

However, the problem I have is that other users will be using this
form by a macro and will be requested to input all the necessary
information because I don't want them messing around in the tables. I
have created a filter query to try to filter the 'number' field and my
criteria reads [Enter: number] but this only allows the user to enter
one number and then the query will execute. Therefore, the user will
only have information that had 1 as a number.

What would be a better criteria to use to allow the user to input
multiple numbers (like I did in the Filter For: 1 or 2 or 3) and have
the query return all the results requested?

Re: Filter multiple results by user input

On Oct 31, 4:43 pm, Barb.Richa...@gmail.com wrote:
> I have created an append query that pulls information from one
> database, and will append the selected information into a new table.
> The fields are setup like 'number' 'category' 'code' 'shares' and
> 'dollars'. Using the "backend" of this table I can filter the numbers
> by right clicking and using Filter For: then enter 1 or 2 or 3 and
> this will return results for all information that has 1 or 2 or 3 as a
> number.
>
> However, the problem I have is that other users will be using this
> form by a macro and will be requested to input all the necessary
> information because I don't want them messing around in the tables. I
> have created a filter query to try to filter the 'number' field and my
> criteria reads [Enter: number] but this only allows the user to enter
> one number and then the query will execute. Therefore, the user will
> only have information that had 1 as a number.
>
> What would be a better criteria to use to allow the user to input
> multiple numbers (like I did in the Filter For: 1 or 2 or 3) and have
> the query return all the results requested?

I hope I understand your question correctly.
I am going to assume that you would like to avoid using code do this -
so we'll try although we'll need to add one command.
Let's call the table that holds your data tInfo.
Create a local table (not on the backend so each user will have their
own separate copy) and call it tCriteria
tCriteria has one field 'Number' (although I would not name it that,
it is a reserved word).
Create a continuous form - let's call it fCriteria. It will show the
data from tCriteria
Create a continuous form - let's call it fInfo.
fInfo's recordsource should be tInfo linked to tCriteria by the
'number' field.
Create a 'single form' view form and call it fMain.
Put both fCriteria and fInfo as subforms in fMain.
Here is the only code:
Go into the properties of fCriteria (make sure it's this form and
not fMain),
go to the events tab,
choose afterupdate,
choose [event procedure],
click on the elipsis (the buton with 3 dots on the end) and it will
take you to a code window.
Your code should look like
Private Sub Form_AfterUpdate()
me.parent.refresh
End Sub
Do the same thing for 'After Del Confirm'
Your code should look like
Private Sub Form_AfterDelConfirm(Status As Integer)
Me.Parent.Refresh
End Sub

Now when you open fmain you can type a number into fCriteria subform
and the data will show in fInfo subform.
Delete the numbers you don't want to see from fCriteria.
Now you can adjust the size and format of your subforms so that
everything looks nice and is an easy size to use.

If you are already proficient in code, you can do something similar,
more elegantly, using a list box and multi selecting items.
Good luck
P

Re: Filter multiple results by user input

Barb.Richards@gmail.com wrote:

> I have created an append query that pulls information from one
> database, and will append the selected information into a new table.
> The fields are setup like 'number' 'category' 'code' 'shares' and
> 'dollars'. Using the "backend" of this table I can filter the numbers
> by right clicking and using Filter For: then enter 1 or 2 or 3 and
> this will return results for all information that has 1 or 2 or 3 as a
> number.
>
> However, the problem I have is that other users will be using this
> form by a macro and will be requested to input all the necessary
> information because I don't want them messing around in the tables. I
> have created a filter query to try to filter the 'number' field and my
> criteria reads [Enter: number] but this only allows the user to enter
> one number and then the query will execute. Therefore, the user will
> only have information that had 1 as a number.
>
> What would be a better criteria to use to allow the user to input
> multiple numbers (like I did in the Filter For: 1 or 2 or 3) and have
> the query return all the results requested?
>
You mention you are creating a macro for the users. Let's say the field
to filter on is called ID and the Append query is called Query1.

Let's say the append query had the following SQL statement
INSERT INTO Table2 ( ID )
SELECT Table1.ID
FROM Table1;

There is no Where clause, nor OrderBy, etc in my example for the the SQL
statement in Query1.

You can have the macro call a function using RunCode, function value
would be
=FilterThisQuery()
Here's a code sample you can call in the macro.

Public Function FilterThisQuery()
Dim qdf As QueryDef
Dim strSQL As String
Dim intPos As Integer

Dim strFilter As String
strFilter = InputBox("Enter 1 or more values. " & _
"Separate multiple values with a comma. " & _
"Ex: 1,2,3", "Enter Values To Filter On")
If strFilter > "" Then
'assumes Query1 is the name of the append query
Set qdf = CurrentDb.QueryDefs("Query1")
strSQL = qdf.SQL

'remove the ; at end of SQL statement if it exists
intPos = InStr(strSQL, ";")
If intPos > 0 Then strSQL = Left(strSQL, intPos - 1)

'now add the filter to the query. Notice space between
'the strSQL and Where.
strSQL = strSQL & " Where ID In (" & strFilter & ");"

'execute the append
CurrentDb.Execute strSQL
Msgbox "Append Complete"
Else
MsgBox "You didn't enter a filter. " & _
"Action Canceled", , "No Action"
End If
End Function

Although this method will work, I wonder if your users do things
manually (via macros) or if they use forms. In an optimal environment
I'd use forms where the folks could select the records to append via a
listbox or some other entry method, you create the SQL statement to
carry out the action, and the form's events calls the append. The
opportunity for users to make mistakes by entering incorrect values is
high with your method.


Caught in a moment
http://www.youtube.com/watch?v=arulHB4lvdU

Re: Filter multiple results by user input

On Nov 1, 2:31 pm, Salad wrote:
> Barb.Richa...@gmail.com wrote:
> > I have created an append query that pulls information from one
> > database, and will append the selected information into a new table.
> > The fields are setup like 'number' 'category' 'code' 'shares' and
> > 'dollars'. Using the "backend" of this table I can filter the numbers
> > by right clicking and using Filter For: then enter 1 or 2 or 3 and
> > this will return results for all information that has 1 or 2 or 3 as a
> > number.
>
> > However, the problem I have is that other users will be using this
> > form by a macro and will be requested to input all the necessary
> > information because I don't want them messing around in the tables. I
> > have created a filter query to try to filter the 'number' field and my
> > criteria reads [Enter: number] but this only allows the user to enter
> > one number and then the query will execute. Therefore, the user will
> > only have information that had 1 as a number.
>
> > What would be a better criteria to use to allow the user to input
> > multiple numbers (like I did in the Filter For: 1 or 2 or 3) and have
> > the query return all the results requested?
>
> You mention you are creating a macro for the users. Let's say the field
> to filter on is called ID and the Append query is called Query1.
>
> Let's say the append query had the following SQL statement
> INSERT INTO Table2 ( ID )
> SELECT Table1.ID
> FROM Table1;
>
> There is no Where clause, nor OrderBy, etc in my example for the the SQL
> statement in Query1.
>
> You can have the macro call a function using RunCode, function value
> would be
> =FilterThisQuery()
> Here's a code sample you can call in the macro.
>
> Public Function FilterThisQuery()
> Dim qdf As QueryDef
> Dim strSQL As String
> Dim intPos As Integer
>
> Dim strFilter As String
> strFilter = InputBox("Enter 1 or more values. " & _
> "Separate multiple values with a comma. " & _
> "Ex: 1,2,3", "Enter Values To Filter On")
> If strFilter > "" Then
> 'assumes Query1 is the name of the append query
> Set qdf = CurrentDb.QueryDefs("Query1")
> strSQL = qdf.SQL
>
> 'remove the ; at end of SQL statement if it exists
> intPos = InStr(strSQL, ";")
> If intPos > 0 Then strSQL = Left(strSQL, intPos - 1)
>
> 'now add the filter to the query. Notice space between
> 'the strSQL and Where.
> strSQL = strSQL & " Where ID In (" & strFilter & ");"
>
> 'execute the append
> CurrentDb.Execute strSQL
> Msgbox "Append Complete"
> Else
> MsgBox "You didn't enter a filter. " & _
> "Action Canceled", , "No Action"
> End If
> End Function
>
> Although this method will work, I wonder if your users do things
> manually (via macros) or if they use forms. In an optimal environment
> I'd use forms where the folks could select the records to append via a
> listbox or some other entry method, you create the SQL statement to
> carry out the action, and the form's events calls the append. The
> opportunity for users to make mistakes by entering incorrect values is
> high with your method.
>
> Caught in a momenthttp://www.youtube.com/watch?v=arulHB4lvdU- Hide quoted text -
>
> - Show quoted text -

Thank you for your response; however, I am not that familiar with
creating SQL statements and whenever I try to change the SQL that has
been written from Access I receive an error message "invalid
bracketing". So far my query returns results from information in my
table. I want to filter multiple numbers; therefore, my criteria for
fund number is [enter: number]. However, I want the user to be able
to enter several numbers with an OR statement, not just 1 number. The
SQL reads:

SELECT TBLataac.[Fund Number], TBLataac.TA, TBLataac.[GL Category],
TBLataac.[TAAC Code], TBLataac.[TA Feed], TBLataac.[Work Date],
TBLataac.[Price Shares], TBLataac.[Price Dollars]
FROM TBLataac
WHERE (((TBLataac.[Fund Number])=[enter: number]))
;

is there anyway to write an OR statement ie. WHERE (((TBLataac.[Fund
Number])=[enter: number or number or number])) so the table can filter
multiple numbers in the same column?

Any assistance is greatly appreciated.