0

I'm working with my boss on a project to keep things more organized in our company. I'm making a template we can use for our monthly reports that: a) print how many of (blank metric here) each team did b) how many distinct computers everyone worked on together (total number of computers worked on without counting the same computer twice) and how many departments are having issues (again distinct, not counting the same department twice).

We are organizing them by date, usually in months so each SQL query has a:

WHERE DATE BETWEEN [Start Date] AND [End Date]

So if we do each query by itself and you can look at everything from the dates you define through user input. The problem is when I have all the queries I need in one form (three subreports in one master report), I have to enter each queries respective [Start Date] and [End Date] - so 6 parameters which is really just the same two done three times.

Is there anyway to fix this using VBA? I haven't messed with VBA much, but I'm pretty comfortable with SQL. Thanks for the help!

Robert Testerman
  • 11
  • 6

1 Answers1

0

There are many, many ways to achieve this. Common ones are:

Use a form to define the start and end date

If you call your form frmStartEnd, and have two textboxes, txtStart and txtEnd, in your query, you can use:

WHERE [DATE] BETWEEN Forms!frmStartEnd!txtStart AND Forms!frmStartEnd!txtStart

You can find more examples about using parameters here. Forms, TempVars and UDFs are applicable to this problem.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I'm assuming frmStartEnd!txtStart is the form labeled frmStartEnd and the txtStart is the label? I tried looking at the link you provided but it kind of went over my head due to not knowing any VBA :( – Robert Testerman Apr 20 '18 at 17:54
  • Almost right, it's a form _named_, not _labeled_ frmStartEnd. Same for the controls. Names and labels are often equal, but don't need to be. It's a bit silly to tag your question [tag:access-vba] if you don't know VBA and can't implement a VBA answer. – Erik A Apr 20 '18 at 17:56