Solved

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

Posted on 2011-03-09
8
431 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
[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
  • 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 500 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
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!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 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