Including parameter fields in report title

[TheHub]

Hi all.
I am running an Access 2003 report that outputs to an Excel Spreadsheet
The parameter query has two paramerters First Date and Last Date.
The report runs from an Button OnClick event.
I need to include the two dates in the 'name' of the spreadsheet as below
Private Sub btn_report_between_dates_Click()
DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, "Calls By Between Dates " First Date" and " Last Date" - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True
End Sub
Can this be done? (btw I know it is preferable to use the TransferSpreadsheet method, but I've not got around to that way yet)
Thanks in advance

 

[pr2-eugin]

If I am reading this correct you have the Form in which you enter the FirstDate and LastDate.. Then Click the button that outputs the Query? If that is correct try the following.. Of course renaming the FirstDate and LastDate to match your Control names..

Private Sub btn_report_between_dates_Click()
    DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, _
                   "Calls By Between Dates " & Fomrat(Me.FirstDate, "dd-mm-yyyy") & " and " & Format(Me.LastDate, "dd-mm-yyyy") & _
                   " - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True
End Sub

PS: Please use Code Tags when posting VBA Code

 

[TheHub]

Almost, when the button is clicked, the query runs and requests the First Date and the Last Date. Don't know if that makes a difference.

 

[pr2-eugin]

Okay try this.. Create two unbound text boxes in the Form you are running the code from, accept the FirstDate and LastDate and use that in the Query..

That is, if your Query is..

SELECT someThing FROM theTable 
WHERE dateField BETWEEN [FirstDate] And [LastDate];

Change it as..

SELECT someThing FROM theTable 
WHERE dateField BETWEEN Forms!yourFormName![FirstDate] And Forms!yourFormName![LastDate];

 

[TheHub]

Thanks. I'll give that a try.

 

[TheHub]

@pr2-eugin. Tried your first suggestion, and many thanks for that, but it results in an error message: Compile error:
Method or data member not found.

Have not tried your second your second method as it will need a rewrite of the form.

 

[pr2-eugin]

Both the methods I proposed say the same thing.. In post#2 I assumed that there already exists two Text Boxes to get the date.. When you mentioned they pop up I understood that you did not have them, so in post#4 I asked you to create them..

 

[TheHub]

I probably have not explained my self properly. What I have is a form, with a button. the button runs a query that ask for the user to input the Fisrt Date and Last Date. It is these two parameters that i need to include into the spreadsheet name. This is the only way I am allowed to do this, as this is the style of the current database. However, thanks for your input, the information you have provided will come in very useful in fututre projects.

 

[TJPoorman]

I don't believe you can grab the arguments from a query the way that you are calling it. You could however use a workaround to accomplish what you are trying to do.

Using global variables and global functions you could get and set the dates for your query. For example:

Place this in a public module:

Public datStartingDate as Date
Public datEndingDate as Date
 
Public Function GetStartDate() as Date
GetStartDate = datStartingDate
End Function
 
Public Function GetEndDate() as Date
GetEndDate = datEndingDate
End Function

Then change your query so that instead of FirstDate and LastDate, call the 2 functions.

Then before your output command set the variable like such:

datStartingDate = InputBox "Please enter starting date"
datEndingDate = InputBox "Please enter ending date"
[COLOR=black]DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, "Calls By Between Dates " & cstr(datStartingDate) & " and " & cstr(datEndingDate) & " - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True[/COLOR]

However pr2-eugin's solution of adding two date controls to your form is much cleaner and simpler.

 

[TheHub]

@TJPoorman. Thanks for the information. I will have to look at all recommendations, as the requirements have now changed.