Parameter for an SQL View

Echo cpctech.

You don't use view with parameters. Views are built with certain relationships to sort and compiling data together for faster reporting purpose. If you couldn't find a relationship, you should create a temporary table or view, populate it using Stored Procedure, print your report and drop the temp view or table.

So there is nothing I can do to the view I have to accept a parameter?

I guess I'll have to add that view to an Access query and place the parameter there?

You meant to create another Query in Access? I thought you wish to retrieve data from MSSQL view?

cpctech's method is the best. Retain the view in your MSSQL.
Create the stored procedure in MSSQL.
Access it through Access to build an instant query and print it to your report, this already solves your problem isn't it?

Ok, this is my stored procedure:

CREATE PROC  mystoredprocedure_customer_activity (@Expr1 varchar)
AS SET NOCOUNT ON
SELECT     TOP 100 PERCENT SUM(dbo.Sales_Detail.Total_Sale_Amt) AS SumTotal_Sale_Amt, MIN(dbo.Sales_Detail.Invoice_Date) AS MinInvoiceDate,
                      MAX(dbo.Sales_Detail.Invoice_Date) AS MaxInvoiceDate, dbo.[Sales Rep Bonus].[Sales Representative], dbo.[Sales Rep Bonus].[Customer Type],
                      dbo.[MAS Customer Info Query 6].Expr1, dbo.[MAS Customer Info Query 6].CustomerName
FROM         dbo.[Sales Rep Bonus] RIGHT OUTER JOIN
                      dbo.[MAS Customer Info Query 6] ON dbo.[Sales Rep Bonus].Customer_No = dbo.[MAS Customer Info Query 6].Expr1 LEFT OUTER JOIN
                      dbo.Sales_Header INNER JOIN
                      dbo.Sales_Detail ON dbo.Sales_Header.Control_No = dbo.Sales_Detail.Header_Control_No INNER JOIN
                      dbo.GPO_Cross_Reference ON dbo.Sales_Header.Dist_Ship_To_ID = dbo.GPO_Cross_Reference.Distributor_Facility_ID ON
                      dbo.[MAS Customer Info Query 6].Expr1 = dbo.GPO_Cross_Reference.PM_Facility_ID
GROUP BY dbo.[Sales Rep Bonus].[Sales Representative], dbo.[Sales Rep Bonus].[Customer Type], dbo.[MAS Customer Info Query 6].Expr1,
                      dbo.[MAS Customer Info Query 6].CustomerName
HAVING      (dbo.[Sales Rep Bonus].[Customer Type] = N'Acute') AND (dbo.[MAS Customer Info Query 6].Expr1 = @Expr1)
ORDER BY dbo.[MAS Customer Info Query 6].Expr1
GO

-----------------------------------------------------------------------------------------------------------------------------
Now, in my passthrough query in Access I put the following? I've never done this before.

currentdb.querydefs("mystoredprocedure_customer_activity ").sql="execute mystoredprocedure_customer_activity " & @Expr1
docmd.openreport

change the @Expr1 to the value you wish to pass to the stored procedure

Note:

dbo.[MAS Customer Info Query 6] is a VIEW, while the others are tables.

adriankohws, that's the thing, I want the user to enter the value, they enter whichever customer
number (Expr1) they wish to run the report on.

OK. Understood your stand point now. I didn't know because usually my interface will be something like allow end-user to specify the parameter in a textbox or whatever, then I just make use of the value to pass to the parameter when print button is clicked.

This is what I have so far:

STORED PROCEDURE:

CREATE PROC  mystoredprocedure_customer_activity (@Expr1 varchar)
AS SET NOCOUNT ON
SELECT     TOP 100 PERCENT SUM(dbo.Sales_Detail.Total_Sale_Amt) AS SumTotal_Sale_Amt, MIN(dbo.Sales_Detail.Invoice_Date) AS MinInvoiceDate,
                      MAX(dbo.Sales_Detail.Invoice_Date) AS MaxInvoiceDate, dbo.[Sales Rep Bonus].[Sales Representative], dbo.[Sales Rep Bonus].[Customer Type],
                      dbo.[MAS Customer Info Query 6].Expr1, dbo.[MAS Customer Info Query 6].CustomerName
FROM         dbo.[Sales Rep Bonus] RIGHT OUTER JOIN
                      dbo.[MAS Customer Info Query 6] ON dbo.[Sales Rep Bonus].Customer_No = dbo.[MAS Customer Info Query 6].Expr1 LEFT OUTER JOIN
                      dbo.Sales_Header INNER JOIN
                      dbo.Sales_Detail ON dbo.Sales_Header.Control_No = dbo.Sales_Detail.Header_Control_No INNER JOIN
                      dbo.GPO_Cross_Reference ON dbo.Sales_Header.Dist_Ship_To_ID = dbo.GPO_Cross_Reference.Distributor_Facility_ID ON
                      dbo.[MAS Customer Info Query 6].Expr1 = dbo.GPO_Cross_Reference.PM_Facility_ID
GROUP BY dbo.[Sales Rep Bonus].[Sales Representative], dbo.[Sales Rep Bonus].[Customer Type], dbo.[MAS Customer Info Query 6].Expr1,
                      dbo.[MAS Customer Info Query 6].CustomerName
HAVING      (dbo.[Sales Rep Bonus].[Customer Type] = N'Acute') AND (dbo.[MAS Customer Info Query 6].Expr1 = @Expr1)
ORDER BY dbo.[MAS Customer Info Query 6].Expr1
GO

------------------------------------------------------------------------------------------------------------------------------

SQL PASS THROUGH QUERY in Access:

DECLARE @Expr1 varchar

EXEC mystoredprocedure_customer_activity @Expr1

-----------------------------------------------------------------------------------------------------------------------------

Access REPORT has the following On Open Event Procedure:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)


    Dim Expr1 As String
    Dim strSQLForSP As String
   
   
    Expr1 = InputBox("Customer Number")
 
    Dim dbs As Database
     Set dbs = CurrentDb

    CurrentDb.QueryDefs("Customer Activity Inquiry by Facility Pass Through").sql = "mystoredprocedure_customer_activity('" & Expr1 & "')"
   
End Sub

------------------------------------------------------------------------------------------------------------------------------

But I'm stuck in the last code (On Open Event Procedure) it's not displaying the report.

Any ideas?

I would recommend you try my solution using your View.  The alternative will be resorting to using ADO with your stored procedure and adding a lot more code that you may ahve difficulty following.

I see you are trying to use a column name as a parameter.  This is not a good idea as you will have to resort to Dynamic SQL.

AcPerkins, if I include: Where CustomerNumber = 'xyz' in my View for the customer number column, how will it know to run the view for the customer number the user decides to use?

I figured it out, I had to create a form that I called in the report in the On Open event.

Thanks!