I have a form with a button that ru

[Cretin]

I have a form with a button that runs a macro that runs a parameter query with a begin date and end date. Is there any way to capture those values so I can use them in the name of the report I.E. report_startdate_enddate?

Cretin

 

[Cretin]

Below is the SQL view. Is it even possible to store the values from the having part of the statement in a table to be overwritten each time the parameter runs?

 SELECT TIME_SUMMARY_ARCHIVE.[PROJECT NAME], Sum(TIME_SUMMARY_ARCHIVE.[HRS THIS WEEK]) AS [SumOfHRS THIS WEEK], TIME_SUMMARY_ARCHIVE.Date
FROM TIME_SUMMARY_ARCHIVE
GROUP BY TIME_SUMMARY_ARCHIVE.[PROJECT NAME], TIME_SUMMARY_ARCHIVE.Date
HAVING (((TIME_SUMMARY_ARCHIVE.Date) Between [Start Date] And [End Date]));

Cretin

 

[MajP]

Do a way with the parameter query. Have a pop up form bound to a table with the startDate and EndDate fields. Then the code should pop open the form and you can enter your dates. Close the form and the query runs pulling the values from the form. Now you have the dates stored for any reuse.

 

[dhookom]

I totally agree with MajP since I think there is no place for parameter prompts in software. However, if you choose to not take his advice, you can try add a text box to your report with a control source of:

=[Name] & " From " & [Start Date] & " to " & [End Date]

Duane
Hook'D on Access
MS Access MVP

 

[Cretin]

ok I will try that. I have the records with various dates in the database. So I can just use a pop up form that I enter the beginning and ending dates in and that will generate the report capturing all the records between those dates?


Cretin

 

[MajP]

You could have a button on your form to run the query/report. The query pulls the criteria values from the form instead of a parameter prompt.
Select ...... Between [Forms] ![ YourFormName]![ StartDate ] And [Forms]![YourFormName]![ EndDate ]

 

[Cretin]

Thank you everyone you were quite helpful. Is what I did is put 2 text boxes on the form. The beginning date and ending date are entered into there. Then I run a macro which runs a query to export the data from the table to a spreadsheet with dates within the range. It then runs some code to rename the spreadsheet. It then clears the date table for the next time.

Function rename_timesheet1()

Dim db As Database

Set db = CurrentDb()
Dim strbegDate As DAO.Recordset
Dim strendDate As DAO.Recordset
Dim LBGSQL As String
Dim LEDSQL As String
Dim LGBG As String
Dim LGED As String
Dim strpath As String
Dim begindate As String
Dim enddate As String






LBGSQL = "select begin_date from tbl_dates_for_report"
LEDSQL = "select end_date from tbl_dates_for_report"


Set strbegDate = db.OpenRecordset(LBGSQL)
Set strendDate = db.OpenRecordset(LEDSQL)
strpath = "U:\Projects\time tracker\testing\"
If strbegDate.EOF = False Then
LGBG = strbegDate("begin_date")
LGED = strendDate("end_date")
Else
LGST = "Not Found"
End If

Name "U:\Projects\time tracker\testing\weekly_project_summary.xlsx" As "U:\Projects\time tracker\testing\weekly_project_summary_" & Format(LGBG, "yyyymmdd") & "_" & Format(LGED, "yyyymmdd") & ".xlsx"

strbegDate.Close
Set strbegDate = Nothing
strendDate.Close
Set strendDate = Nothing

Cretin