Us e the Format function to format your dates by quarter
Format(<yourdatefield>,"q") will return the quarter for the date as 1, 2, 3, or 4.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, January 02, 2013 12:04 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Using a table for quarterly queries date ranges
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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of jfakes.rm
Sent: Wednesday, January 02, 2013 5:57 PM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.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
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar