INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

I have a form with a button that ru

I have a form with a button that ru

(OP)
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

RE: I have a form with a button that ru

(OP)
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?

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

RE: I have a form with a button that ru

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.

RE: I have a form with a button that ru

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

RE: I have a form with a button that ru

(OP)
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

RE: I have a form with a button that ru

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 ]

RE: I have a form with a button that ru

(OP)
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.

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 = Nothing 

Cretin

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close