[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I make a master [Start Date] and [End Date]?

Posted on 2011-03-09
8
Medium Priority
?
437 Views
Last Modified: 2012-05-11
Please forgive me if this answer is easily answered and found elsewhere on this site but I have not been able to figure this out.

I am also VERY new to this so please don't assume I know too much when answering.

I have a query that is composed of several other crosstab queries. The master query will be used to make a report. Each crosstab query is limited by the same date range. The date range will never differ between the crosstabs.

I want the end result report to have a user entered date range from a form and I only want to enter it once. As it stands now, if I want the range to change, I must go into each query and manually change the date. I tried using the "Between [Start Date[ and [End Date]" criteria but right now, it must be entered for every query and gets very cumbersome (And to be honest, I havent actually got that to work correctly) Also, I am unable to control the date range from just one query and join it to the rest, I tried it and the integrity of the information was lost. So if thats the only way to do it, then I'd have to figure out how to rebuild the whole thing.

So, is there a way to have a form with a user generated date range that will be applied to all the queries used to generate the report?
0
Comment
Question by:IRISDoan
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35084463
<I am also VERY new to this so please don't assume I know too much when answering.>
Then just be aware that what you are asking for here *requires* you to be familiar with: Form design, SQL, VBA coding, ...etc)
So it is not clear how much handholding you may need.

If it were me, I would create a form to enter the dates.
Then store these dates in Public variables.
In a public module:
Public dtmpubStartDate as Date

In your code somewhere use something like this to set the variable:
dtmpubStartDate =YourStartDateTextBox

Then retrieve these values (as query criteria in your "main" query) with public functions.
Public Function GetStartDate() as date
    GetStartDate=dtmpubStartDate
End Public

So the query would look something like this:
SELECT fld1, fld2
FROM SomeTable
WHERE fld2=GetStartDate()


See how far you get with this...

;-)

JeffCoachman
0
 
LVL 3

Accepted Solution

by:
AccessYourBiz_Com earned 2000 total points
ID: 35085239
Using a form to capture the dates is one of the most common ways to handle this. To set this up follow these steps,

1)create the form
2)add two text boxes to the form
3)set the format of the two text boxes as a Date Format
4)Let's say the name of the form is frmDateRange and the name of the text boxes are dteStartDate and dteEndDate.
Open your queries and replace the [Start Date] and [End Date] with the form name and text box name in the proper access format. ie. Forms![frmDateRange]![dteStartDate].
So the criteria for each query may look like this:
Between Forms![frmDateRange]![dteStartDate] and Forms![frmDateRange]![dteEndDate]
That will do it.  Good luck
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35086417
AccessYourBiz_Com,

I used the Public function as a way to avoid the issue Crosstabs have with referencing Form Controls directly:


untitled.JPG
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35086465
0
 
LVL 3

Expert Comment

by:AccessYourBiz_Com
ID: 35086568
Good job boaq2000, I didn't realize there was an issue with Crosstab querys. I never use them.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35087276
...and I try to avoid them...

(SQL is not my strong point...)

;-)

Jeff
0
 

Author Closing Comment

by:IRISDoan
ID: 35096242
This solution worked great. You left out one part though. In each crosstab you have to define the perameters for the start date and end date as date/time.

So I set the two perameters as follows:
[Forms]![TSSNumbers]![StartDate]  -  Date/Time
[Forms]![TSSNumbers]![EndDate]  -  Date/Time

Doing this stopped the error messages that the other user displayed.
0
 
LVL 3

Expert Comment

by:AccessYourBiz_Com
ID: 35096398
Great, I am glad it worked for you! :-)
Thanks for the info on the necessary parameter.
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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

640 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