• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Access: How to update DATE of a query without changing every query to reflect a new date?

I am creating a bunch on different tables based on counting errors and such from different tables/forms which is then used for reporting. In these tables, I need to specify which month or months. For example in January I would have to manually go into every querry and type in the criteria: <2/1/2011 and >1/1/2011.

I want to be able to change it all at once or use a form/table to do this.

Please HELP

  • 4
  • 2
1 Solution
I'm a little confused so I will try to restate the question...   let me know if I have it right

You have a series of Queries that us a Date range as a filtering criteria?  and you want to use this date range across all of the queries?

If so this is easy....

Create two global vars...  Lets say gdatStartDate and gdatEndDate each declared as Date types
You would allow the user to set these two vars and

Create two Functions to retrieve these vars
GetStartDate, GetEndDate

Your filtering criteria would become Between GetStartDate() and GetEndDate()

Before I go further...  let me know if I understand your question
kwarden13Author Commented:
This is exactly what I want I think. If this will make it so I don't ahve to go into each query to change then date, then THIS IS WHAT I AM LOOKING FOR!

Please tell me more!

First make a new standard module
Copy the attached code and paste it into the new module

Now for the boring part...

Open each of your queries and replace your date criteria with

Between GetStartDate()  AND GetEndDate()

Then you need to create a form that the user can set the two dates with

A form with 2 fields
Test to confirm that the values are dates
And in the form close or After update of each of the fields...   you would assign the values to the variables

IE:  if isDate(Me.txtStartDate) then    
           gdatStartDate = Me.txtStartDate
            Some error catching code
      end if

do something like this for the EndDate as well
Public gdatStartDate as Date
Public gdatEndDate as Date

Public Function GetStartDate() as Date
    GetStartDate = gdatStartDate 
End Function

Public Function GetEndDate() as Date
    GetEndDate = gdatEndDate 
End Function

Open in new window

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

BTW:   My understanding is that you need to run a series of reports with the same date range...

If this is the case using the global vars is your best bet...


If you are only running one report at a time and each time would be for a different date range

you can simplify the process by prompting the user for dates from within the query

Replace your existing criteria.... with

Between [Enter a Starting Date] AND [Enter an Ending Date]

And it will prompt the user with a small input box.... for them to put in the values...

Drawback to this method...  is you can't validate the input to confirm they are valid dates
At the risk of sounding dense...
What does "not needed" mean?

When I clarified the question, the response was...
"THIS IS WHAT I AM LOOKING FOR!   Please tell me more!"
kwarden13Author Commented:
I will award you the points, i just dont need a solution anymore
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now