Unique Query

[Amanda S]

I have an Access Database for which I need a unique query written and I can't
find how to do this anywhere.

I need to write a query that will allow users of the database to be able to
check off what they want to see in the output. This database will be used by
many and each of the users will not need the same query. Instead of writing
a ton of queries, I'd like to put all the fields on a form or something and
let them check off what information they need to see. For example user A only
needs to see the records with an order number and delivery date. User B only
needs to see the payment date and delivery date. All of the data will be
based upon one form's information.

Please keep in mind that the users of this database are not familiar with
Access so they won't understand how to use a query wizard. This is why I
would like to create one form that everyone can use because their needs for
the information are going to change.

 

[Marshall Barton]

I have an Access Database for which I need a unique query written and I can't
find how to do this anywhere.

I need to write a query that will allow users of the database to be able to
check off what they want to see in the output. This database will be used by
many and each of the users will not need the same query. Instead of writing
a ton of queries, I'd like to put all the fields on a form or something and
let them check off what information they need to see. For example user A only
needs to see the records with an order number and delivery date. User B only
needs to see the payment date and delivery date. All of the data will be
based upon one form's information.

Please keep in mind that the users of this database are not familiar with
Access so they won't understand how to use a query wizard. This is why I
would like to create one form that everyone can use because their needs for
the information are going to change.

Sounds like a good case for using code to construct the
query.

Try this kind of arrangement:

First create a query (named qryUtility). It doesn't matter
what the query is based on, just pick a table and one field.

Then create an unbound form and add a multi select list box
(named lstFields) to the form's header section. Set its
RowSourceType set to FieldList and its RowSource to the
table/query that hold the data. (You can then use its
ItemsSelected property to construct the query's Field list.)

Add a subform control (named sfmData) to the detail section
and make it as large as you reasonably can make it. Do not
specify any of its properties.

Finally add a command button next to the list box in the
header section and use something like this code in the
button's Click event procedure:

Dim varItem As Variant
Dim strFields As String

If Me.lstFields.ItemsSelected.Count > 10 Then
MsgBox "Too many fields"
Exit Sub
End If

For Each varitem In Me.lstFields.ItemsSelected
strFields = strFields & "," _
& Me.lstFields.ItemData(varitem)
Next varItem

CurrentDb.QueryDefs!qryUtility.SQL = _
"SELECT " & Mid(strFields, 2) & " FROM Absences"

Me.sfmData.SourceObject = "Query.qryUtility"