Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007

    Red face Unanswered: Help writing an expression

    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.

    My questions:

    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.

    I'd appreciate any help you can provide.


  2. #2
    Join Date
    Jan 2007
    California, USA
    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.

  3. #3
    Join Date
    Mar 2007
    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.


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    mike, good luck to you

    your situation sounds unenviable

    that your company should need to let someone go is sad, but it does happen

    that the person who was let go should destroy stuff on his way out is a very sad commentary on his ethics

    but that your company should ask you to jump into access development with no experience is hugely sad, and says a whole lot about the company, doesn't it | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts