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

Kelly
kwarden13Asked:
Who is Participating?
 
RgGray3Connect With a Mentor Commented:
OK...

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
      else
            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

0
 
RgGray3Commented:
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
0
 
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!
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
RgGray3Commented:
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...

HOWEVER...

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
0
 
RgGray3Commented:
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!"
0
 
kwarden13Author Commented:
I will award you the points, i just dont need a solution anymore
0
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.

All Courses

From novice to tech pro — start learning today.