Ms-access – Print filter parameters on a report

ms accessparameter

I'm using MS Access 2010.

I have a form with a number of fields of various types (number, text, lookup, checkbox).

Users can right click on any field to filter the form.

Then I have a button that generates a report based on this filtered data:

Private Sub Filter_and_Report_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "WallsRpt", acViewPreview, , strWhere
End Sub

My question is how to list the fields that were filtered in the report (probably in the header/footer).

Best Answer

The Filter condition is in Me.Filter: you use it above.

You'd have to parse this, but you must have set it previously to be able to apply it. Ignoring SQL Injection risk (I just got this off the interwebs), you know in this snippet you are filtering on ProductCatID

Me.Filter = "ProductCatID = """ & Me.cboShowCat & """"
Me.FilterOn = True

You can set a new variable/property (eg Me.Tag) for use in the Header/Footer

Related Solutions

Named Parameters in SQuirreL SQL

Figured this out, it's easy, you just add :paramName in your query and it automatically asks your for the parameters when you run the query.

Sql-server – SQL Linked Server Passing Date Parameters

For an ad hoc batch, you need to type the date values and assign them to the variables:

DECLARE @fromperiod varchar(8),
        @toperiod   varchar(8);

SELECT @fromperiod = '20130101',
       @toperiod = '20130131';

An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the toolbar, and then editing the values in the Locals tab, but this seems to be a pretty cumbersome way to avoid a little typing:

There are probably also ways you could use templates or snippets to have a slightly easier way to input values, but I don't find the above that much of a blocker in the first place.

For Reporting Services, I know there are ways to allow users to enter data and have those passed at parameters, but someone else is going to have to give you the skinny on that (the documentation might be helpful as well). I can spell Reporting Services but not much more than that. If this is a stored procedure it probably makes things a lot easier.

Best Answer

Related Solutions

Named Parameters in SQuirreL SQL

Sql-server – SQL Linked Server Passing Date Parameters

Related Question