DataBase - Module for Date Filter for Stored Procedure

Asked By Brett Barry: Go Get Geek!

12-Nov-09 08:34 PM

Hello,

Can someone explain how I can create code to act as a parameter for a
stored procedure?

For example, in the following stored procedure:

sp_report ProfitAndLossStandard show Text, Label, Amount parameters
DateFrom = {d'yyyy-mm-dd'},
DateTo =  {d'yyyy-mm-dd'}, SummarizeColumnsBy = 'TotalOnly'

I would like to be able to generate results using parameters for each
quarter of the current calendar year.

The first quarter would make the parameter DateFrom = {d'2009-01-01'},
DateTo = {d'2009-03-31'}.

There would end up to be 4 queries, one for each quarter based on the
current year.

I am not sure if I am going about this right, but if anyone can explain
the best way to do this or provide useful links, it is much
appreciated.

Thanks!

13-Nov-09 12:28 AM

Can you explain what version of Access and SQL Server you are using?
How are you connecting to SQL Server (odbc, adp, other)?

I am not familiar with this notation:
{d'yyyy-mm-dd'}
Where/how is this used?

Are you trying to open a form? Run a report? Get a recordset (dao or
ado)?

-Tom.
Microsoft Access MVP

13-Nov-09 11:25 AM

I am using Access 2007 and a connection to a QuickBooks database via
an ODBC driver.


This is the date format for the parameter for filtering reports using
custom dates via DateFrom & DateTo. Otherwise, there is the parameter
DateMacro =3D 'LastCalendarQuarter' etc.
See http://doc.qodbc.com/qodbc/20/reports/sp_report_parameter_values0bbb.ht=
ml?sp_report_name_id=3D1



Before I had someone help me with a form to pull customer data and the
selection on the form was used as the parameter in the stored
procedure:

Private Sub Command7_Click()

Dim strSQL As String
strSQL =3D "sp_report JobEstimatesVsActualsDetail show Text, Label, " &
_
& _
=3D'" & _
Me.CustomerName & "',SummarizeColumnsBy =3D 'TotalOnly'"
Debug.Print strSQL
CurrentDb.QueryDefs("JobEstvsActuals").SQL =3D strSQL

End Sub

However, that was pulling the list of customers via a list box and
selection was done that way.


Perhaps on the form for this project, there are date pickers, one for
the date to and one for the date for and these dates are inserted in
the stored procedure, which is a make-table query to store the
results. Or, I could hard code the dates for each quarter, but there
would have to be some code to fill in the current year (yyyy). This is
eventually going to be used in a report.

16-Nov-09 09:47 AM

The general technique of Command7_Click (what a terrible name for an
event procedure) is correct. Of course such code should be generalized
and put in a central location. You will need to find out how your
destination database wants date values delimited. Probably by
single-quotes.

-Tom.
Microsoft Access MVP