Parameter Query for Multiple Columns with drop box of choices

[Fat Jack Utah]

I need to be able to make a Form with filters or query elements or a
Parameter Query which will allow me to set parameters for multiple columns
while controling what can be used as parameter.

I am familier with [What state] options, but am wondering if all the
parameters can be on one page (maybe a form) so all can be set before it
finds the results

AND

if the info in the parameter input box can be linked to have option of
"ALL", or a list of items which are in that column (like the FILTER does in
EXCEL)

HOW THIS WOULD BE USED

I have a table with Dates, Times, People and locations

I would like to be able to set my parameters all at one time, before getting
the results
DATE -- Between --- and -----
TIME -- Between ---- and -----
PEOPLE - Select either ALL, or one name from the list of names in the column
of that table
Locations -- Select ALL or a specific location (from the list of available
location - so I dont mispell and not find info)

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article contains a downloadable sample database that illustrates how to
build such a form. It builds the filter string from just the boxes where the
user entered some criteria.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, you can refer to Controls on a Form like this:

PARAMETERS Forms!TheFormName!TheControlName TheDataType;
SELECT ...
FROM ...
WHERE SomeColumnName = Forms!TheFormName!TheControlName

E.g.:

PARAMETERS Forms!frmCriteria!txtBeginDate Date;
SELECT...
FROM...
WHERE begin_date >= Forms!frmCriteria!txtBeginDate

If you have a ComboBox where the user has selected "ALL" you can do this
in the WHERE clause:

WHERE IIf(Forms!FormName!ComboBoxName<>"ALL", column_name =
Forms!FormName,ComboBoxName, True)

An If...Then example of the above is:

If Forms!FormName!ComboBoxName<>"ALL" Then

compare the value of "column_name" to the selected item
in the ComboBox

Else

select all rows, because the user has selected "ALL" in
the ComboBox.

End If

If you're using a ListBox you'll have to create a function in the
Criteria Form that will put the selected items into a comma-delimited
string that the query will use in the WHERE clause.

Note: Be sure to set up strings like this:

strStrings = "'abc','def','ghi'"

- use single quotes for individual items

And numbers like this:

strNumbers = "123, 456, 123, 809"


Then, I usually put the comma-delimited string in a module variable and
use a Public Function that the query will access to get the string.
E.g.:

Module's Declaration Section:

Private m_StringName As String
=============
Public Function CommaDelimitedString() As String
CommaDelimitedString = m_StringName
End Function

Then I use the function name in the PARAMETERS clause of the query, and
use the InStr() trick to parse each item in the delimited string:

PARAMETERS Forms!FormName.CommaDelimitedString Text(255);
SELECT ...
FROM ...
WHERE Instr("," & Forms!FormName.CommaDelimitedString & "," , "," &
column_name & ",")>0

Note the period between the form name and the function name.

If one, or more, of the items in the delimited string are values in the
indicated column (column_name), then the Instr() function returns a
value greater than (>) zero, and the table row is selected.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSSNZJIechKqOuFEgEQJksgCfQ3mWoPxN8nv3uI+ZUHvBYGn28SYAn1P2
ujvIYKyL5KUC921jVepv21XF
=a0Yv
-----END PGP SIGNATURE-----

I need to be able to make a Form with filters or query elements or a
Parameter Query which will allow me to set parameters for multiple columns
while controling what can be used as parameter.

I am familier with [What state] options, but am wondering if all the
parameters can be on one page (maybe a form) so all can be set before it
finds the results

AND

if the info in the parameter input box can be linked to have option of
"ALL", or a list of items which are in that column (like the FILTER does in
EXCEL)

HOW THIS WOULD BE USED

I have a table with Dates, Times, People and locations

I would like to be able to set my parameters all at one time, before getting
the results
DATE -- Between --- and -----
TIME -- Between ---- and -----
PEOPLE - Select either ALL, or one name from the list of names in the column
of that table
Locations -- Select ALL or a specific location (from the list of available
location - so I dont mispell and not find info)