stored procedure and pass through

Question

stored procedure and pass through

bill.akin

Ten Centuries

Points: 1158
More actions
September 28, 2011 at 9:41 am

#248485

I have a pass through query that is like:
execute spsocialcount '[BeginDate]','[EndDAte]','[Holiday]';
I have also used:
execute spsocialcount '[forms]![f_main]![outreach_begin_date]','[forms]![f_main]![outreach_end_date]','[Holiday]'
both these say that they cannot convert varchar to date.
the stored procedure is:
USE [BtcSystem]
GO
/****** Object: StoredProcedure [dbo].[spsocialcount] Script Date: 09/28/2011 10:08:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spsocialcount]
@BeginDate char(10),
@EndDate char(10),
@Holiday char(10)
as
SELECT tblMemberNameandAddress.LastName, tblMemberNameandAddress.FirstName, member_time.date, Count(member_time.date) AS NumberOfSocials
FROM tblMemberNameandAddress INNER JOIN member_time ON tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE member_time.date Between convert(datetime, @BeginDate) And convert(datetime, @EndDate) and member_time.time_out > '12/30/1899 16:30:0' OR member_time.date=convert(datetime,@Holiday)
GROUP BY tblMemberNameandAddress.LastName, tblMemberNameandAddress.FirstName, member_time.date
ORDER BY member_time.date;

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

What is the exact date string you're passing? You may have to run a profiler trace to catch this. And is there any reason your input variables can't be datetime instead of char(10)?

bill.akin Ten Centuries Points: 1158 More actions · Answer 2

for now date string is 5/1/11, 5/31/11 and 5/30/11. i have made variables datetime with same error

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Can you post the create table definitions for the Member_Time table?

It sounds like the member_time.date or member_time.time_out fields are not datetime fields and are probably varchar, and you have at least one record in there that can't be converted.

Try doing

select cast(date as datetime) from member_time

select cast(time_out as datetime) from member_time

I'd be willing to bet you get an error on one of those selects..

bill.akin Ten Centuries Points: 1158 More actions · Answer 4

I did not get an error on either select and if i run the execute on sql side with literals it pulls data just fine

vince_sql Right there with Babe Points: 722 More actions · Answer 5

Have you tried casting it as char and passing that to the stp?

http://sqlvince.blogspot.com/[/url]

bill.akin Ten Centuries Points: 1158 More actions · Answer 6

Thank you for all of your help. It appears that the forms!f_main!outreach_begin_date variables are not passing the value of the variable to the stored procedure. it is just passing a literal forms!f_main!outreach_begin_date to the procedure. I need to determine a way that the execute will pass the value of the variable to the stored procedure.

bill.akin Ten Centuries Points: 1158 More actions · Answer 7

I placed the following code on the button that opens the report:

Dim db As DAO.Database

Dim qdef As DAO.QueryDef

Set db = CurrentDb

Set qdef = db.QueryDefs("qrysocialcountpassthrough")

qdef.SQL = "execute spsocialcount '" & Forms!f_main!outreach_begin_date & "', '" & Forms!f_main!outreach_end_date & "', '" & InputBox("What is the holiday date?") & "'"

qdef.close

db.close

DoCmd.OpenReport "rptMemberSocialDailyCount", acViewPreview

works like a dream. Thanks for all of your help.