You're welcome, John.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@...> wrote:
>
> 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 (7) |
Tidak ada komentar:
Posting Komentar