While I am fairly comfortable navigating most Microsoft programs, I have never used Access before this week.
I recently was hired to replace a manager let go by the firm. Prior to leaving, he deleted all of the reports and queries used by the staff in the department. He did leave behind the database. As he was the one who wrote these reports, and compiled and reported the data, none of the other members of the company knew how to re-create them (a foolish mistake on thier part to be sure). I was asked to see if I could create new reports based on the existing database.
I have been able to re-create most of the queries and reports sucessfully, but I am having a problem undertanding how to write an expression for one of the reports.
This report looks at "start dates" and "end dates" for employees working on particular projects. It somehow calculates the total number of days that a person worked at a particular job.
1) How do I write the expression to calculate this figure based on the "start date" and "End Date" fields in the query and report?
2) Where do I place this expression once written? (Does it only go in the design of the report, or does it need to show somewhere in the query as well?)
I realize that this may seem an almost juvenile question to even an intermediate user of Access, but, as I said, I never even looked at the program prior to a week ago.
Mike, there is nothing juvenile in Access. It's just that some of us have been here longer than others. And it sounds like you have not been here too long yet. Welcome to the forum and to Access.
If the report is doing something besides just showing the days spent on a project, then putting the calculation in the report should be just fine. Here is the calculation for number of days. This will include weekend days.
NumberOfDaysWorked = DateDiff("d", EndDate, StartDate)
This calculation can go into the Control Source of the text box on the report. Start at the equal sign (leave off the "NumberOfDaysWorked"). I'm assuming that StartDate and EndDate accompany each project and you are printing a line on the report for each project.
Vic....Thanks for the response. I am new to the program but in the short week that I have been using it, I'm already seeing a myriad of possible applications.
I'll try your solution on Monday when I get back to work.