Pass Through Quiery

[gumby]

I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.

Is thier a way to pass parameters to a pass through query?

SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
AS [Signed PP]
FROM dbo.tblPersActionLog INNER JOIN
dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
(dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory.PersActionID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersActionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)


Thanks,
David

 

[Duane Hookom]

I use DAO code to change the SQL property of the saved P-T query.
CurrentDb.QueryDefs("qsptMyPT").SQL = "EXEC spMyPT '" & Me.txtStart & "', '"
& Me.txtEnd & "'"

 

[gumby]

I use DAO code to change the SQL property of the saved P-T query.
CurrentDb.QueryDefs("qsptMyPT").SQL = "EXEC spMyPT '" & Me.txtStart & "', '"
& Me.txtEnd & "'"
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCPhttp://www.access.hookom.net/UCP/Default.htm

I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.

Is thier a way to pass parameters to a pass through query?

SELECT     COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
                      AS [Signed PP]
FROM         dbo.tblPersActionLog INNER JOIN
                      dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE     (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
                      (dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory.PersActionID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersActionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
                      (dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)

Thanks,
David- Hide quoted text -

- Show quoted text -

Duane,
I think I got a start, but I need the sql to look like this.

EXEC rpt_RecruitmentNumbersOverall @Start="11/1/2007",
@End="12/1/2007"

With the following VBA I am getting this:


Public Sub Test()
CurrentDb.QueryDefs("pt_qry_RecuitmentNumbersOverall").SQL = "EXEC
rpt_RecruitmentNumbersOverall " & "@Start=" & Forms!form1!txtDate1 &
", " & "@End=" & Forms!form1!txtDate2
End Sub

I get this:
EXEC rpt_RecruitmentNumbersOverall @Start=11/1/2007, @End=12/1/2007

How do I get it to add the quotes around the dates?

Thanks,
David

 

[gumby]

I use DAO code to change the SQL property of the saved P-T query.
CurrentDb.QueryDefs("qsptMyPT").SQL = "EXEC spMyPT '" & Me.txtStart & "', '"
& Me.txtEnd & "'"

I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.
Is thier a way to pass parameters to a pass through query?
SELECT     COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
                      AS [Signed PP]
FROM         dbo.tblPersActionLog INNER JOIN
                      dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE     (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
                      (dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory.PersActionID) = 0)
AND
([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersActionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
                      (dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
Thanks,
David- Hide quoted text -

- Show quoted text -

Duane,
I think I got a start, but I need the sql to look like this.

EXEC rpt_RecruitmentNumbersOverall @Start="11/1/2007",
@End="12/1/2007"

With the following VBA I am getting this:

Public Sub Test()
CurrentDb.QueryDefs("pt_qry_RecuitmentNumbersOverall").SQL = "EXEC
rpt_RecruitmentNumbersOverall " & "@Start=" & Forms!form1!txtDate1 &
", " & "@End=" & Forms!form1!txtDate2
End Sub

I get this:
EXEC rpt_RecruitmentNumbersOverall @Start=11/1/2007, @End=12/1/2007

How do I get it to add the quotes around the dates?

Thanks,
David- Hide quoted text -

- Show quoted text -

I got it. I need to ' instead of ". I didn't think the pass through
query would accept single quotes.

Thanks again -
David

 

[John W. Vinson]

How do I get it to add the quotes around the dates?

Use "" within a string delimited by " in order to insert a single " character.

John W. Vinson [MVP]