Option Menu for queries

[misschanda via AccessMonster.com]

I need some help/guidance in the following,

I was told from boss that he would like to have a form that gave him options
of different parameters (size/temperature) from a table. Once he chose each
parameter a report would show with the options he choose. I know in a query
you can have a table with and choose the field names you want, than use that
to make a report; however am not quite sure how to have the option menu he
suggested with all the fields of the table operate as a query and produce a
report as you could in Report Design.

Thanks for the help
LA

 

[Klatuu]

You don't need to have any filtering in the query. Use the Where argument of
the OpenReport method. It is the same as an SQL WHERE clause without the
word Where. You can build the Where condition string in your form based on
the user's selections. Then use the string in the Where argument of the
OpenReport method.

For example, let's say you have a txtSize control and a txtTemp control on
the form and you want to use them for filtering. Now, in a command button
the produces the report:

Dim strWhwere As String

If Not IsNull(Me.txtSize) Then
strWhere = "[Size] = '" & Me.txtSize & "'"
End If
If Not IsNUll(Me.txtTemp) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Temperature] = " & Me.txtTemp
End If

Docmd.OpenReport "MyReport", , , strWhere

Not that in the example above, the field Size is text and Temperature is
numeric.

 

[KARL DEWEY]

I am not totally clear on the extent of what you want. But you can have
check boxes on a form that correlate to your data fields. Use the calculated
fields (IIF statement) to determine whether to pull data from that field. In
the report set the Can Grow property to Yes and drag the text box size to a
hairline. The textbox will not display if there is no data.

You can do selective sorts in a similiar fashion by having a seperate query
for each sort. Have an additional field named Sort_Order in the output that
the report Sorting and Grouping will use. Control whether any records will
be pulled if that query is not the selected sort order. Put all of your sort
queries in a union query. Only the selected sort will produce records.