Automating report with query parameters

[Dale via AccessMonster.com]

I have created a query in Access 97 that requires a date range. This query populates a report that the user would like e-mailed on a nightly basis.

I cannot pass the parms required for the query. I am using RunCode command from macros to run DoCmd sendobject.

This report must be totally automated.

Any suggestions would be helpful.

 

[Steve Schapel]

Dale,

Where does the date range come from?

 

[Dale via AccessMonster.com]

I need to calcualte the starting date and will use the current system date as teh end date.

 

[Steve Schapel]

Calculate?

 

[Dale via AccessMonster.com]

Yes. I am calculating the beginning of the month. The report should run for the first of each month to the current date.

 

[Steve Schapel]

Dale,

I am sorry, I still haven't quite grasped the meaning of your question.
Is there anything else you would like to share with us, that might
help give a picture of what you are trying to achieve, and what the
problem is?

 

[Dale via AccessMonster.com]

I have a parameter driven query requiring a starting & ending date. This query is tied to a report that summarizes the data as required.

The report (or query if it is easier) must run nightly with no user interaction and be e-mailed to the user.

I am trying to submit a parameter-driven report (or query) from a macro in Access 97 and cannot seem to do it.

 

[Steve Schapel]

Dale,

Can you please give an example of "parameter driven query"? Maybe post
the SQL view of your existing query.

In Access, you can have a Parameter Query, but this is incompatible with
"no user interaction".

 

[Immanuel Sibero]

Hi

I may be off base here. But from the description of your problem. Instead of
a parameterized query, can you not just use a query with criteria?
As you mentioned, you have "calculated" the beginning of the current month.
I suppose using a function, something like:


Function fBegOfCurMonth() As Date
fBegOfCurMonth = DateSerial(Year(Date), Month(Date), 1)
End Function



In your query, use a criterion:

Between fBegOfCurMonth() and Date()

Assuming your system date is always correct, this criterion will always
result in the date range between the first of current month and today.



HTH,
Immanuel Sibero

I have a parameter driven query requiring a starting & ending date. This

query is tied to a report that summarizes the data as required.

The report (or query if it is easier) must run nightly with no user

interaction and be e-mailed to the user.

I am trying to submit a parameter-driven report (or query) from a macro in

Access 97 and cannot seem to do it.

 

[George Nicholson]

Here's one way. I'm sure there is a more elegant solution, but until one of
us finds it....
I only have a single parameter and it's a Time, rather than a date, but this
may help point you in a satisfying direction.

Note that my Parameter is ONLY in the criteria of a specific field. It is
NOT a full Sql PARAMETER (i.e., it is not set under query properties). If it
is also set up as an Sql PARAMETER, the Replace() simply won't work. If it
were, I suppose you could easily manipulate the text string to ignore
everything before SELECT.

Public Sub TestReport()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dtmStart As Date
Dim strOldSQL As String
Dim strNewSQL As String

' Given1: query "qrptStartingTimes" with a Parameter in the criteria for
a field (NOT as a parameter property of the query)
' Given2: a report based on that query

' You'll have to decide how to set this value dynamically
dtmStart = #9:00:00 AM#

Set db = CurrentDb
Set qdf = db.QueryDefs("qrptStartingTimes")
' Get the query's current SQL
strOldSQL = qdf.SQL
' Swap the parameter string for a value & assign it to the query
strNewSQL = Replace(strOldSQL, "[Please enter a StartingTime]", "#" &
dtmStart & "#")
qdf.SQL = strNewSQL

' Change acPreview to acNormal if you want to print automatically
DoCmd.OpenReport "rptStartingTimes", acPreview

' Restore the query's original SQL
qdf.SQL = strOldSQL
Set qdf = Nothing
Set db = Nothing
End Sub

another way to do it (in this case I believe you have to have the sql
Parameter property set):
- Set up your query to be a MakeTable or append-to-tempTable query.
- qdf![Enter a StartingTime] = #4:00 AM# ' Feeding the parameter this
way only seems to work with Execute?
- db.Execute dbFailOnError ' Execute can't be
used on SELECT queries
- Open the report that is now based on the Table you just created rather
than a query
- Empty or delete the temp table

HTH,