I’m looking at creating a query by form stored procedure for an access project file utilizing Microsoft’s guidelines in Article 235539 ( which is listed below:
>CREATE Procedure "QBFProc"
>@CustomerID varchar(10), @EmployeeId int, @OrderDate >datetime
>As
>Declare @SQLString varchar(1000)
>Declare @SelectList varchar(100)
>SET NOCOUNT ON
>SELECT @SelectList = 'SELECT * FROM Orders'
>--Check to see if CustomerID search criteria is NULL.
>--If it contains a value, Begin to construct a WHERE >clause.
>IF @CustomerId Is NOT NULL
> BEGIN
> SELECT @SQLString = 'WHERE CustomerID >= ''' + @CustomerId + ''''
> END
>
>--Check to see if EmployeeID search criteria is NULL.
>--If it contains a value, add additional information to
>--the WHERE clause.
>
>IF @EmployeeID Is NOT NULL
> BEGIN
> IF @SQLSTRING Is NOT NULL
> BEGIN
> SELECT @SQLSTRING = @SQLSTRING +
> ' AND EmployeeID = ' + Convert(varchar(100), @EmployeeID)
> END
> ELSE
> BEGIN
> SELECT @SQLSTRING = 'WHERE >EmployeeID = ' +
> Convert(varchar(100), >@EmployeeID)
>
> END
> END
>
>--Check to see if OrderDate search criteria is NULL.
>--If it contains a value, add additional information to
>--the WHERE clause.
>
>IF @OrderDate Is NOT NULL
> BEGIN
> IF @SQLSTRING Is NOT NULL
> BEGIN
> SELECT @SQLSTRING = @SQLSTRING +
> ' AND OrderDate = ''' + Convert(varchar(20), @OrderDate) + ''''
> END
> ELSE
> BEGIN
> SELECT @SQLSTRING = 'WHERE >OrderDate = ''' +
> Convert(varchar(20), >@OrderDate) + ''''
> END
> END
>--Concantinate the SELECT list and WHERE clause together.
>SELECT @SelectList = @SelectList + ' ' + @SQLString
>--Execute the result
>EXECUTE(@SELECTLIST)
Currently, I’m using a stored procedure to return the results that I need (which is listed below), but I really prefer to create a form-based query, instead of a series of numerous annoying popups, which I currently get.
How do I go about in identifying two related tables to query in Microsoft’s example above? I have tried several times but can't seem to do anything that saves or works.
CREATE PROCEDURE dbo.StoredProcedure1(@ENTER_STATE nvarchar(2),
@ENTER_COUNTY nvarchar(50),
@ENTER_TOWNSHIP nvarchar(3),
@ENTER_TOWN_DIR nvarchar(1),
@ENTER_RANGE nvarchar(3),
@ENTER_RANGE_DIR nvarchar(1),
@ENTER_SECTION nvarchar(3))
AS SELECT dbo.Header.Property_ID, dbo.Header.Old_Property_ID, dbo.Header.Agreement_Type, dbo.Header.Status,
dbo.Header.Lessor_Acreage, dbo.Header.Total_Acreage, dbo.Header.Received_date, dbo.Header.Forwarded_date, dbo.Header.Approved_date,
dbo.Header.Effective_date, dbo.Header.Expiration_date, dbo.Header.Product, dbo.Header.Status, dbo.Location.State, dbo.Location.County,
dbo.Location.Township, dbo.Location.Township_Dir, dbo.Location.Range, dbo.Location.Range_Dir, dbo.Location.Section,
dbo.Location.[Description (Qtr-Qtr)]
FROM dbo.Location INNER JOIN
dbo.Header ON dbo.Location.Property_ID = dbo.Header.Property_ID
WHERE (dbo.Location.State LIKE @ENTER_STATE) AND (dbo.Location.County LIKE @ENTER_COUNTY) AND
(dbo.Location.Township LIKE @ENTER_TOWNSHIP) AND (dbo.Location.Township_Dir LIKE @ENTER_TOWN_DIR) AND
(dbo.Location.Range LIKE @ENTER_RANGE) AND (dbo.Location.Range_Dir LIKE @ENTER_RANGE_DIR) AND
(dbo.Location.Section LIKE @ENTER_SECTION)
ORDER BY dbo.Location.State, dbo.Location.County, dbo.Location.Township, dbo.Location.Range, dbo.Location.Section
GO
CREATE PROCEDURE dbo.Location_Search_Texas(@ENTER_STATE nvarchar(2),
@ENTER_COUNTY nvarchar(50),
@ENTER_ABSTRACT nvarchar(10),
@ENTER_SURVEY nvarchar(50))
AS SELECT dbo.Header.Property_ID, dbo.Header.Old_Property_ID, dbo.Header.Agreement_Type, dbo.Header.Status,
dbo.Header.Lessor_Acreage, dbo.Header.Total_Acreage, dbo.Header.Received_date, dbo.Header.Forwarded_date, dbo.Header.Approved_date,
dbo.Header.Effective_date, dbo.Header.Expiration_date, dbo.Header.Product, dbo.Header.Status, dbo.Location.State, dbo.Location.County,
dbo.Location.[Abstract (Texas)], dbo.Location.[Survey (Texas)]
FROM dbo.Location INNER JOIN
dbo.Header ON dbo.Location.Property_ID = dbo.Header.Property_ID
WHERE (dbo.Location.State LIKE @ENTER_STATE) AND (dbo.Location.County LIKE @ENTER_COUNTY) AND
(dbo.Location.[Abstract (Texas)] LIKE @ENTER_ABSTRACT) AND (dbo.Location.[Survey (Texas)] LIKE @ENTER_SURVEY)
ORDER BY dbo.Location.State, dbo.Location.County, dbo.Location.[Abstract (Texas)], dbo.Location.[Survey (Texas)]
GO
>CREATE Procedure "QBFProc"
>@CustomerID varchar(10), @EmployeeId int, @OrderDate >datetime
>As
>Declare @SQLString varchar(1000)
>Declare @SelectList varchar(100)
>SET NOCOUNT ON
>SELECT @SelectList = 'SELECT * FROM Orders'
>--Check to see if CustomerID search criteria is NULL.
>--If it contains a value, Begin to construct a WHERE >clause.
>IF @CustomerId Is NOT NULL
> BEGIN
> SELECT @SQLString = 'WHERE CustomerID >= ''' + @CustomerId + ''''
> END
>
>--Check to see if EmployeeID search criteria is NULL.
>--If it contains a value, add additional information to
>--the WHERE clause.
>
>IF @EmployeeID Is NOT NULL
> BEGIN
> IF @SQLSTRING Is NOT NULL
> BEGIN
> SELECT @SQLSTRING = @SQLSTRING +
> ' AND EmployeeID = ' + Convert(varchar(100), @EmployeeID)
> END
> ELSE
> BEGIN
> SELECT @SQLSTRING = 'WHERE >EmployeeID = ' +
> Convert(varchar(100), >@EmployeeID)
>
> END
> END
>
>--Check to see if OrderDate search criteria is NULL.
>--If it contains a value, add additional information to
>--the WHERE clause.
>
>IF @OrderDate Is NOT NULL
> BEGIN
> IF @SQLSTRING Is NOT NULL
> BEGIN
> SELECT @SQLSTRING = @SQLSTRING +
> ' AND OrderDate = ''' + Convert(varchar(20), @OrderDate) + ''''
> END
> ELSE
> BEGIN
> SELECT @SQLSTRING = 'WHERE >OrderDate = ''' +
> Convert(varchar(20), >@OrderDate) + ''''
> END
> END
>--Concantinate the SELECT list and WHERE clause together.
>SELECT @SelectList = @SelectList + ' ' + @SQLString
>--Execute the result
>EXECUTE(@SELECTLIST)
Currently, I’m using a stored procedure to return the results that I need (which is listed below), but I really prefer to create a form-based query, instead of a series of numerous annoying popups, which I currently get.
How do I go about in identifying two related tables to query in Microsoft’s example above? I have tried several times but can't seem to do anything that saves or works.
CREATE PROCEDURE dbo.StoredProcedure1(@ENTER_STATE nvarchar(2),
@ENTER_COUNTY nvarchar(50),
@ENTER_TOWNSHIP nvarchar(3),
@ENTER_TOWN_DIR nvarchar(1),
@ENTER_RANGE nvarchar(3),
@ENTER_RANGE_DIR nvarchar(1),
@ENTER_SECTION nvarchar(3))
AS SELECT dbo.Header.Property_ID, dbo.Header.Old_Property_ID, dbo.Header.Agreement_Type, dbo.Header.Status,
dbo.Header.Lessor_Acreage, dbo.Header.Total_Acreage, dbo.Header.Received_date, dbo.Header.Forwarded_date, dbo.Header.Approved_date,
dbo.Header.Effective_date, dbo.Header.Expiration_date, dbo.Header.Product, dbo.Header.Status, dbo.Location.State, dbo.Location.County,
dbo.Location.Township, dbo.Location.Township_Dir, dbo.Location.Range, dbo.Location.Range_Dir, dbo.Location.Section,
dbo.Location.[Description (Qtr-Qtr)]
FROM dbo.Location INNER JOIN
dbo.Header ON dbo.Location.Property_ID = dbo.Header.Property_ID
WHERE (dbo.Location.State LIKE @ENTER_STATE) AND (dbo.Location.County LIKE @ENTER_COUNTY) AND
(dbo.Location.Township LIKE @ENTER_TOWNSHIP) AND (dbo.Location.Township_Dir LIKE @ENTER_TOWN_DIR) AND
(dbo.Location.Range LIKE @ENTER_RANGE) AND (dbo.Location.Range_Dir LIKE @ENTER_RANGE_DIR) AND
(dbo.Location.Section LIKE @ENTER_SECTION)
ORDER BY dbo.Location.State, dbo.Location.County, dbo.Location.Township, dbo.Location.Range, dbo.Location.Section
GO
CREATE PROCEDURE dbo.Location_Search_Texas(@ENTER_STATE nvarchar(2),
@ENTER_COUNTY nvarchar(50),
@ENTER_ABSTRACT nvarchar(10),
@ENTER_SURVEY nvarchar(50))
AS SELECT dbo.Header.Property_ID, dbo.Header.Old_Property_ID, dbo.Header.Agreement_Type, dbo.Header.Status,
dbo.Header.Lessor_Acreage, dbo.Header.Total_Acreage, dbo.Header.Received_date, dbo.Header.Forwarded_date, dbo.Header.Approved_date,
dbo.Header.Effective_date, dbo.Header.Expiration_date, dbo.Header.Product, dbo.Header.Status, dbo.Location.State, dbo.Location.County,
dbo.Location.[Abstract (Texas)], dbo.Location.[Survey (Texas)]
FROM dbo.Location INNER JOIN
dbo.Header ON dbo.Location.Property_ID = dbo.Header.Property_ID
WHERE (dbo.Location.State LIKE @ENTER_STATE) AND (dbo.Location.County LIKE @ENTER_COUNTY) AND
(dbo.Location.[Abstract (Texas)] LIKE @ENTER_ABSTRACT) AND (dbo.Location.[Survey (Texas)] LIKE @ENTER_SURVEY)
ORDER BY dbo.Location.State, dbo.Location.County, dbo.Location.[Abstract (Texas)], dbo.Location.[Survey (Texas)]
GO