I currently have an Access 2000 ADP form which is being supplied by a string variable s which contains the name of the stored
Procedure and its paramters
(e.g s = sp_Prn_IC @sdtStart= '2002-12-12' , @sdtEnd= '2003-12-12' , @InvType= 'I' , @AmountFrom= 0 , @AmountTo= 999999.99)
Please note that I can run this string directly under SQL Query Analyzer with no problems.
In the form I have a List 'QItems' and I do not know how to supply the List with above string s.
The List displays blank screen.
I have a relevant subroutine :

'' ===========
Private Sub QItems1_Enter()
    Me.QItems.RowSourceType = "Table/View/StoredProc"
    Me.QItems.RowSource = s
    Me.QItems.ColumnCount = 13
    Me.QItems.ColumnHeads = True
    Me.QItems.Requery
End Sub

'' ===========
(N.B. under debug shows the value of s correctly. )

The Stored Procedure is :
-- =========
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_Prn_IC
(@InvType nvarchar(1),
@sdtStart VARCHAR(10), -- yyyy-mm-dd
@sdtEnd VARCHAR(10), -- yyyy-mm-dd
@AmountFrom float =NULL,
@AmountTo float =NULL)
AS
DECLARE @dtStart AS datetime
DECLARE @dtEnd AS datetime

SET NOCOUNT ON

SET @dtStart = cast(@sdtStart AS DATETIME )
SET @dtEnd = cast(@sdtEnd AS DATETIME )
SELECT dbo.tblInvoices.InvNum, dbo.tblInvoices.InvoiceDate,
                      dbo.tblInvoices.AccCode, dbo.tblInvoices.AccName, dbo.tblInvoices.Amount,
                      dbo.tblAddress.ADDCODE, dbo.tblAddress.Address1, dbo.tblAddress.Address2,
                      dbo.tblAddress.Address3, dbo.tblAddress.Address4, dbo.tblAddress.Address5,
                      dbo.tblAddress.Postcode, dbo.tblInvoices.PrintInvoice
FROM dbo.tblInvoices
INNER JOIN
             dbo.tblAddress ON dbo.tblInvoices.AddressID = dbo.tblAddress.ID
WHERE (CONVERT(NVARCHAR(10), dbo.tblInvoices.InvoiceDate, 120) BETWEEN @dtStart AND @dtEnd ) AND
      (dbo.tblInvoices.InvType = @InvType) AND
          ( dbo.tblInvoices.Amount BETWEEN @AmountFrom AND @AmountTo)
ORDER BY dbo.tblInvoices.InvNum

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- =============

(N.B. under debug shows the value of s correctly. )

Any help will be greatly appreciated.

Narendra Rathod

Narendr Rathod

Try changing to
Brian Skelton
Braxis Computer Services Ltd.

Hi Brian,

I have tried as shown:
EXEC dbo.sp_Prn_IC @sdtStart= '2002-12-13' , @sdtEnd= '2003-12-13' , @InvType= 'I' , @AmountFrom= 0 , @AmountTo= 999999.99
and no Luck.

Many thanks

Narendra Rathod

Narendr Rathod

Is the owner of the stored procedure dbo?

Try leaving the owner out and see if that makes a difference.


Brian Skelton
Braxis Computer Services Ltd.

Hi Brian,

I promise you I have tried these minor variations which you mention and had no luck as the sample shows below:
EXEC sp_Prn_IC @sdtStart= '2002-12-14' , @sdtEnd= '2003-12-14' , @InvType= 'I' , @AmountFrom= 0 , @AmountTo= 999999.99.

Over the weekend I had to caught out some serious money at London’s bookstores to seek a cure and will let you know when I have found it.

Many thanks.

convert (... 120)

nitpicking observation... are you using appropiate constant?


thanks,

Howard
[email protected]