Insufficient number of arguments were supplied for procedure or functi

[tgtfranz]

I have a function that I am calling in a subfomr. I am getting the error: Insufficient number of arguments were supplied for procedure or function.

When I run this in SQL it returns exactly what I want with the exact same parameters that I am entering in Access.

Please help.
Thanks

 

[drtree]

Hey - what do you mean by "function"? Are you running a stored procedure on SQL Server? Could you post some of the code?

doc tree

 

[tgtfranz]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PopulatePayTypesandHoursSummaryByDateRange]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[PopulatePayTypesandHoursSummaryByDateRange]
GO


CREATE FUNCTION dbo.PopulatePayTypesandHoursSummaryByDateRange
(
@EnterEmpNo int
,@EnterStartingCheckDate datetime
,@EnterEndingCheckDate datetime
)
RETURNS TABLE

AS

RETURN

(SELECT DISTINCT
pc.emp_no
, pcpa.pay_type
, pt.description
, SUM(pcpa.hours) AS Hours
, SUM(pcpa.cmw_gross) AS Gross

FROM
pay_checks AS pc
INNER JOIN
pay_checks_pay_assoc AS pcpa
ON
pc.pay_checks = pcpa.pay_checks
INNER JOIN
dbo.pay_types AS pt
ON
pcpa.pay_type = pt.pay_type

WHERE
pc.emp_no = @EnterEmpNo
-- 6111
AND
CONVERT(smalldatetime, pc.check_date)
BETWEEN @EnterStartingCheckDate
-- '2000-01-01 00:00:00'
AND @EnterEndingCheckDate
-- '2000-06-30 00:00:00'

GROUP BY
pc.emp_no
, pcpa.pay_type
, pt.description
)

 

[JackVam]

could you send an example of the call?

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[LNBruno]

Please don't double post.

http://www.tek-tips.com/viewthread.cfm?qid=1410519&page=1

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[tgtfranz]

JackVam,

What do you mean by the call?

This data is called when the form opens. The main form is populated from a sproc. There is currently no VBA in this form.

Please clarify.

LNBruno,

I added this to MS Access Forum when I thought this is more of an Access thing that SQL Server possibly.

 

[JackVam]

Hi

What I was getting was to see that the actual code you were sending through to SQL Server was as you expected. i.e , if you were to open Profiler , and trapped the statement coming from your application what do you see?

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[tgtfranz]

Hi JackVam.

I changed this to use a function rather than a sproc. Now I am able to get the data that I need but I am prompted twice for the same parameters that are in the main form. I thought that is what I was eliminating using this code.

Is this the code you are looking for:

Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = "Select * from dbo.PopulatePayTypesandHoursSummaryByDateRange(" _
& Parent.emp_id & ", '" _
& Parent.BeginningDate & "', '" & Parent.EndingDate & "')"
End Sub

Thanks for the assistance.