Solved

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

Posted on 2011-02-15
8
334 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
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 500 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now