not receiving records in ADP

A

Ashfaq Parkar

i have created sp using one temporary table, which works excellent in SQL
Query Analyzer. However

when I used the same sp in Access Data Project with :

1) Datasheet view, receiving the following message

“the stored procedure executed successfully but did not return recordsâ€

2) Report

“provider command for child rowset does not produce a rowsetâ€

can anybody help me? i have attached sp.

Thanks and kind regards

*******************
here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF


--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE TABLE #tempAIOI (strCo CHAR(2), strAcct CHAR(4), strAcctTitle
CHAR(30), strSubCode CHAR(5), strSubCodeTitle CHAR(30), strRef CHAR(6),
strTran CHAR(3), strVouch CHAR(6), strVouchDate DATETIME, curAmount DEC(11,2))
INSERT INTO #tempAIOI
SELECT *
FROM tblAIOI
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate,
@AgingDate AS [Aging_UpTo],

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 0 AND 30
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_0_30',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 31 AND 60
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_31_60',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 61 AND 90
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_61_90',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 91 AND 120
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_91_120',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 121 AND 150
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_121_150',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 151 AND 180
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_151_180',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 181 AND 270
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_181_270',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 271 AND 360
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_271_360',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 361 AND 540
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_361_540',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 541 AND 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_541_720',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) > 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_G_720'

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,
#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
R

Rod Gill

Hi,

This is a Microsoft Project group, you need to post your question in a
Microsoft Access group such as public.access.adpsql

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: www.projectvbabook.com


Ashfaq Parkar said:
i have created sp using one temporary table, which works excellent in SQL
Query Analyzer. However

when I used the same sp in Access Data Project with :

1) Datasheet view, receiving the following message

"the stored procedure executed successfully but did not return records"

2) Report

"provider command for child rowset does not produce a rowset"

can anybody help me? i have attached sp.

Thanks and kind regards

*******************
here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF


--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE TABLE #tempAIOI (strCo CHAR(2), strAcct CHAR(4), strAcctTitle
CHAR(30), strSubCode CHAR(5), strSubCodeTitle CHAR(30), strRef CHAR(6),
strTran CHAR(3), strVouch CHAR(6), strVouchDate DATETIME, curAmount
DEC(11,2))
INSERT INTO #tempAIOI
SELECT *
FROM tblAIOI
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate,
@AgingDate AS [Aging_UpTo],

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 0 AND 30
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_0_30',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 31 AND 60
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_31_60',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 61 AND 90
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_61_90',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 91 AND 120
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_91_120',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 121 AND 150
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_121_150',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 151 AND 180
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_151_180',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 181 AND 270
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_181_270',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 271 AND 360
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_271_360',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 361 AND 540
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_361_540',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 541 AND 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_541_720',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) > 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_G_720'

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,
#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Click to expand...
 
A

Ashfaq Parkar

dear Rod Gill

thanks for ur mail
unfortunately i unable to locate "public.access.adpsql" group under
Microsoft Access in fact it is not there

cand u pls. give me exact group name with main group so that i can post.
thanks and kind regards
ashfaq.parkar



Rod Gill said:
Hi,

This is a Microsoft Project group, you need to post your question in a
Microsoft Access group such as public.access.adpsql

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: www.projectvbabook.com


Ashfaq Parkar said:
i have created sp using one temporary table, which works excellent in SQL
Query Analyzer. However

when I used the same sp in Access Data Project with :

1) Datasheet view, receiving the following message

"the stored procedure executed successfully but did not return records"

2) Report

"provider command for child rowset does not produce a rowset"

can anybody help me? i have attached sp.

Thanks and kind regards

*******************
here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF


--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE TABLE #tempAIOI (strCo CHAR(2), strAcct CHAR(4), strAcctTitle
CHAR(30), strSubCode CHAR(5), strSubCodeTitle CHAR(30), strRef CHAR(6),
strTran CHAR(3), strVouch CHAR(6), strVouchDate DATETIME, curAmount
DEC(11,2))
INSERT INTO #tempAIOI
SELECT *
FROM tblAIOI
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate,
@AgingDate AS [Aging_UpTo],

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 0 AND 30
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_0_30',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 31 AND 60
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_31_60',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 61 AND 90
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_61_90',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 91 AND 120
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_91_120',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 121 AND 150
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_121_150',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 151 AND 180
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_151_180',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 181 AND 270
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_181_270',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 271 AND 360
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_271_360',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 361 AND 540
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_361_540',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 541 AND 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_541_720',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) > 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_G_720'

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,
#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Click to expand...
Click to expand...
 
J

JulieS

Hi Ashfaq,

Try the following URL
http://www.microsoft.com/office/com...oft.public.access.modulesdaovba&lang=en&cr=US

Or if you are using the web interface, try the Access Database > Programming
link.

I hope this helps. Let us know how you get along.

Julie


Ashfaq Parkar said:
dear Rod Gill

thanks for ur mail
unfortunately i unable to locate "public.access.adpsql" group under
Microsoft Access in fact it is not there

cand u pls. give me exact group name with main group so that i can post.
thanks and kind regards
ashfaq.parkar



Rod Gill said:
Hi,

This is a Microsoft Project group, you need to post your question in a
Microsoft Access group such as public.access.adpsql

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: www.projectvbabook.com


Ashfaq Parkar said:
i have created sp using one temporary table, which works excellent in SQL
Query Analyzer. However

when I used the same sp in Access Data Project with :

1) Datasheet view, receiving the following message

"the stored procedure executed successfully but did not return records"

2) Report

"provider command for child rowset does not produce a rowset"

can anybody help me? i have attached sp.

Thanks and kind regards

*******************
here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF


--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE TABLE #tempAIOI (strCo CHAR(2), strAcct CHAR(4), strAcctTitle
CHAR(30), strSubCode CHAR(5), strSubCodeTitle CHAR(30), strRef CHAR(6),
strTran CHAR(3), strVouch CHAR(6), strVouchDate DATETIME, curAmount
DEC(11,2))
INSERT INTO #tempAIOI
SELECT *
FROM tblAIOI
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate,
@AgingDate AS [Aging_UpTo],

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 0 AND 30
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_0_30',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 31 AND 60
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_31_60',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 61 AND 90
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_61_90',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 91 AND 120
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_91_120',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 121 AND 150
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_121_150',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 151 AND 180
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_151_180',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 181 AND 270
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_181_270',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 271 AND 360
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_271_360',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 361 AND 540
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_361_540',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) BETWEEN 541 AND 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_541_720',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate,
101)) - #tempAIOI.strVouchDate)) > 720
THEN #tempAIOI.curAmount
ELSE 0
END) AS 'D_G_720'

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,
#tempAIOI.strRef,
#tempAIOI.strTran,
#tempAIOI.strVouch,
#tempAIOI.strVouchDate

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

not receiving records in ADP 1
not receiving records in ADP 2
Stored procedure works in Query Analyser, not ADP 3
Numaric to Inword in excel by VB 1
The procedure executed successfully but did not return records 3
Using ADP Subreports with Stored Procedures with Parameters 3
create calendar event using external data 1
attaching an event to an email 1

Top