Converting SQL which refers to screen controls using a parameter table

support@ascassociates.biz , Sales Enquiries

Converting SQL which refers to screen controls using a parameter table

Shown below is a typical example of an Access Query which combines the use of IIF statements and form controls to deliver a dataset controlled by what a user has selected in a form.

The first example shows a very simple query which refers to a screen field on a form

SELECT BigCustomers.Country, BigCustomers.*
FROM BigCustomers
WHERE (((BigCustomers.Country)=[Forms]![frmPrompt]![txtCountry]));

The next example is more complicated, the additional complexity using IIF statements allows the form parameters to be optionally supplied by the user.

SELECT IIf(Not IsNull([Forms]![frmPrompt]![txtTheDate]),IIf([Forms]![frmPrompt]![txtTheDate]<[Date],1,0),1) AS Expr1,
IIf(Not IsNull([Forms]![frmPrompt]![txtCountry]),IIf([Forms]![frmPrompt]![txtCountry]=[Country],1,0),1) AS Expr2,
BigCustomers.*
FROM BigCustomers
WHERE (((IIf(Not IsNull([Forms]![frmPrompt]![txtTheDate]),IIf([Forms]![frmPrompt]![txtTheDate]<[Date],1,0),1))=1)
AND ((IIf(Not IsNull([Forms]![frmPrompt]![txtCountry]),IIf([Forms]![frmPrompt]![txtCountry]=[Country],1,0),1))=1));

This article explains a method for converting queries like this to use either Pass-Through Queries or Views on SQL Server.

Creating The Parameter Table

The first step is to create a table in SQL Server which will hold a users parameters, this table will be shared by all users, but a filter will be used to ensure that each user only works with there selections.

-- Create a table into which parameters will be inserted

CREATE TABLE tblParameters(
SelectedCountry VARCHAR(15),
SelectedDate DATETIME,
SelectedRegion VARCHAR(15),
UserName VARCHAR(128) DEFAULT suser_name()
)

Creating The Users View

Next we create the user specific view, into which a user will update their parameters.

-- Create a View through which a user can see their parameters

CREATE VIEW vw_tblParameters
AS
SELECT * FROM tblParameters
WHERE UserName = suser_name()

Converting The SQL To A View

In this case we could use either a Pass Through Query, or as shown below create a view on SQL Server, and then link the view to Access.

The first example shows how we do this for the simpler query, notice how the parameters view does not have any joining fields to the main table (called a cross-join or cartesian product), this projects the parameter against each row in the target table acting to filter the rows.

CREATE VIEW vw_ResultsSimple
AS
SELECT *
FROM BigCustomers
CROSS JOIN vw_tblParameters
WHERE [Country] = [SelectedCountry]

For the more sophisticated query allowing optional paramters we use a COALESCE functions to ensure that the view works when any combination of parameters are completed by a user. IF for example a user does not complete the country parameter then the COALESCE function (which takes the first not null value) becomes ' ' = ' ' which will always be true and thus ignores the filter, but if the country parameter has a value 'France', then the function becomes equivalent to 'France' = [Country].

CREATE VIEW vw_Results
AS
SELECT *
FROM BigCustomers
CROSS JOIN vw_tblParameters
WHERE COALESCE ([Country], ' ') = COALESCE ([SelectedCountry], [Country], ' ') AND
COALESCE ([Region], ' ') = COALESCE ([SelectedRegion], [Region], ' ') AND
COALESCE ([Date], ' 1/1/1900') >= COALESCE ([SelectedDate], [Date], '1/1/1900')

For a numerical field we could use COALESCE ([ANumber], 0) >= COALESCE ([SelectedNumber], [ANumber], 0)

Linking The Views

Top of Page

The next step is to link Access to the views.

Changes To The Form

If you don’t want to write any program code, and the form is not already bound to another table or query, then the prompting form needs simply to be bound to the vw_tblParameters, and you need to make sure that if a parameter is not supplied then the field contains a value of null.

If you have a more complex form, then you need to use some programming code to open the vw_tblParameters, clear all unused parameters to NULL, and save all the screen parameters.

One final comment is that the view of the parameters table must remain updateable, some program code is provided in our article on Relinking Tables And Views to help with this if you need to relink the view.

Download Code

The sample code can be downloaded from here Access 2000 Database.

MUST+SQL automatically identifies all your Access query and screen parameters and generates parameter tables on SQL Server. Click below to improve your development productivity.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz