Solved

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

Posted on 2011-03-09
8
429 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

809 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