Unfortunately, the database is not relational. I see about 26 fields in a
table. The report is based off of the table. The report reflects the number
of calls made to each individual in the maintenance department. One
expression tracks how many calls were made for the specific month. Then with
the individual's name, how many calls were processed and then a %. The
report is run from a button. For instance when the user clicks on Jan button
this report that has all of the months for the year is run. So if you click
Jan button, only Jan will have numbers. When Feb is run the report will have
numbers for Jan and Feb and etc.
Jan Feb Mar.....Dec
Number of 182 118 83 90
Requests
No. Processed 66 58 65 25
by Jane Doe
% Processed 36% 58% 37% 25%
by Jane Doe
No. Processed 12, etc
by Jean Smith
% Processed by 10%, etc,
Jean Smith
Each field has an expression that has the year date explained above. For
all fields under Jan the date range is Jan 1 thru Feb 1. For all fields
listed under Feb the date range is Feb 1 thru Mar 1.
I have to do this for all the fields numerous times. I was hoping to run a
parameter query that would pull up the correct month. The fields used in the
expressions are Call In Date (date w/time); Completed By; Completed
By-Second. When I tried running a parameter query, it was giving me
difficulty because the date is associated with the time. I was thinking
perhaps subreports would work better but the parameter query was not pulling
in the records.
I hope this helps and I appreciate this so much. I know Access well but SQL
and VBA very little though I can take a code and make it work referencing my
own fields.
John Spencer said:
Well, to fix the immediate problem you could probably use something like the
following.
=DCount("[Call In Date]","Maintenance Info","([Completed By]='Jane Doe' Or
[Completed By-Second]='Jane Doe') And ([Call In Date]
=DateSerial(Year(Date()),1,1) And
[Call In Date]< DateSerial(Year(Date()),2,1))")
HOWEVER, I would venture to guess that there would be a much simpler way to
fix this problem. In order to figure that out we would need to know more
about the data structure and more about how these reports vary.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
jlo wrote:
I have inherited a database that has reports for each month of the year.
Inside the report are well over 50 fields that have expressions. In each
expression, there is a date referenced. By the beginning of the next year, I
have to change the current year to the new year." This is so tedious. Isn't
there a way to do this better? Here is a sample of one expression. At the
end of the year, I will have to go into 200 of these fields and change the
year date to the new year.
Fields: Call In Date (date w/time); Completed By; Completed By-Second
=DCount("[Call In Date]","Maintenance Info","([Completed By]=Jane Doe' Or
[Completed By-Second]='Jane Doe') And ([Call In Date] >=#01/01/2008# And
[Call In Date]< #02/01/2008#)")
Any suggestions?
