John V and Bill,
Thanks for your help. I was hoping this would be easy (it never is with me). I think I will create a form and enter the dates I need, at least I would only have to change all the dates in one place.
John F.
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> John F.
>
> Going with what John V. suggested, I use a FiscalYear table in a lot of my applications because our company's year starts in July. The table has the fiscal year, period, StartDate, EndDate and Quarter. These are all filled in each year by a function I wrote. So when we get near a new fiscal year I run the function to add the data. What complicated things was we were running a 13 period year with 4 weeks per period. Luckily, we are now using a 12-period year.
>
> All that being said, you could put a combo box with the year, quarter, start date and end date on a form. Also put two textboxes that are hidden. Those would have a control source of the combo's date columns.
>
> Another way would be to have the user enter the start date and have a function to add 2 months to that date for the end date.
>
> The user picks the year/quarter and the textboxes get the dates to be used by the query.
>
> Capeesh?
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > John-
> >
> > You cannot reference data in a table like that using a Parameter. I suppose
> > you could bind the table to a form that's open, then you can reference the
> > form controls in your query.
> >
> > Note, however, that you cannot use a Parameter to replace an expression.
> > Between X And Y is an expression. Parameters can be used only to replace
> > single values in a SELECT or WHERE clause. You could, for example, do:
> >
> > BETWEEN [Forms]![frmDateParms]![txtFromDate] And
> > [Forms]![frmDateParms]![txtToDate]
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> > Sent: Wednesday, January 02, 2013 5:57 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Using a table for quarterly queries date ranges
> >
> > Ok, I hope I can explain this. Quarterly I have to update a complicated
> > report. This report has numerous sub-reports hence about 10 different
> > queries to aggregate everything.
> >
> > The queries have to count the following items:
> > 1. New cases, open cases, and closed cases by quarter (Q1, Q2, Q3,Q4), as
> > well as count the same things for the year to date (ie if you run the report
> > in Q3, how many total new open closed cases are there. I thought I would be
> > the range of dates that I'm looking for in a separate field in the table.
> > 2. How many cases there were by department and location.
> > 3. How much money was spent.
> > You get the idea.
> >
> > My questions is, instead of updating all of the queries, how can I enter the
> > dates in a table and point the queries to the dates in that one table.
> >
> > I tried a test query using: [Tables]![tblAnnualDateRange].[Q1]
> > For that field the data I entered in the table: Between #1/1/2012# and
> > #3/31/2012 (the query needs a date field, however, I used a text field to
> > see if i could get the quarter range). The query didn't work and asked for
> > the Q1 parameter.
> >
> > Also, some of the calculations in the report should only be based of of the
> > previous quarter. So if someone runs the report in Q3, only Q1 and Q2 data
> > should be displayed. To get around this, I thought about leaving Q3 blank
> > for example, until Q4.
> >
> > Hopefully I've explained this enough for someone to help get me started.
> >
> > I might be better off doing some sort of date calculations in the queries,
> > however, I thought I might start off using dates in a table.
> >
> > John F.
> >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar