Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I have a form with a button that ru
- Thread starter Cretin
- Start date
- Thread starter
- #2
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?
Cretin
Code:
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
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.
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
=[Name] & " From " & [Start Date] & " to " & [End Date]
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #5
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
Cretin
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 ]
Select ...... Between [Forms] ![ YourFormName]![ StartDate ] And [Forms]![YourFormName]![ EndDate ]
- Thread starter
- #7
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.
Cretin
Code:
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 = NothingCretin
- Status
Similar threads
- Locked
- Question
- Locked
- Question