Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
8 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

721 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