Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-15
8
Medium Priority
?
396 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:kwarden13
  • 4
  • 2
6 Comments
 
LVL 11

Expert Comment

by:RgGray3
ID: 34900735
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
 

Author Comment

by:kwarden13
ID: 34900776
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
 
LVL 11

Accepted Solution

by:
RgGray3 earned 2000 total points
ID: 34901106
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 11

Expert Comment

by:RgGray3
ID: 34901202
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
 
LVL 11

Expert Comment

by:RgGray3
ID: 35150463
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
 

Author Comment

by:kwarden13
ID: 35151317
I will award you the points, i just dont need a solution anymore
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question