Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-09
8
Medium Priority
?
434 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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