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 (4) |
Tidak ada komentar:
Posting Komentar