Solved

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

Posted on 2011-03-09
8
427 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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35086465
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
combine an MS SQL string in Idera DM 9 31
How to make an ADE file by code? 11 42
Max per month 3 15
DSum between dates 5 11
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

759 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

18 Experts available now in Live!

Get 1:1 Help Now