Recent content by jeromez

The following code calcuates the difference in work days between two dates: Function BusinessDays(PosHireDate, RepDate) As Long BusinessDays = (DateDiff("d", PosHireDate, RepDate) - _ (DateDiff("ww", PosHireDate, RepDate) * 2) + 1) + _ (Weekday(PosHireDate) = vbSunday) + _...

thank you RuralGuy! I got it to work. I've attached an example of the database I created to test the code given to my by Carl Dobbs (thanks Carl). Taking that code I created a form (see "Form1") and also used that function in the query (see query "Qry_ElapsedWorkDays") (thanks RuralGuy for...

I've searched, but I can't make much sense of the results. It looks like i have to have VBA code and record that as a function. I'm not sure how I can incorporate that into my query so that the expression calculates the difference. I need a little guidance =)

I have two dates: StartDate = 11/09/2009 ReportDate = 11/30/2009 I know that days elapsed is simply: [ReportDate] - [StartDate] = 21 Days But how do I get just WORKDAYS? The result would be 16 days in this example.

You're right! I guess I've been looking at this too long =) Thanks! One more item: I attached a screen shot of the query in Access that I use to run the report (see file Qry_Pivot5.xls) using the information in my query, is there any way to create the report so that the months go across...

I wrote a report which averaged by dept. the number of FTE (Full Time Equivalent or Headcount). However, If a manager has more than one department I need to add or sum the average of those departments. See attached file for an example. Is there any way to do this? Thanks! Jerome

The report will only have 12 months. See attached JPEG of the report - notice I circled what needs to be based on a condiditon - I changed the labels, but carried over the last month as placeholders going forward At this moment I do have all 12 months listed on the report, but because I...

I have a report that I created currently has months Jul – Nov by Department Departments listed as rows Months listed as columns FTE listed as value The report is based on a pivot query I created. As the new month’s data is added to the table and then added to the query, I don’t want to...

PROBLEM SOLVED! I knew I would get it if I kept looking, because it didn't make sense one would work and the other subquery wouldn't Well the problem was that in the table the "Data Type" for "RepDate" field I incorrectly or inadvertently put "text" when obviously it should have been...

send. mbd is attached it looks like it didn't get attached to previous post

I still don't have any resolution with my minor problem so I was hoping someone could look at the two databases attached: 1 send.mbd contains the query "Qry_One" which you can see has a "blank" for the Dec period 2. Jerome.mdb which I created to test the formula on the subquery and it...

Hi Bob: I agree the subquery is still not right. When I remove "A.Deptno = Tbl_Hours_Act.DeptNo" and just sum YTD by time without the DeptNo field then everything is fine. Everything adds up except the last month of the year (i.e., December) in the YTDHours field of "Query 1" except...

Okay, I think I almost have it. See attached database "Almost_Final.mdb" and look at "Query 1" This gives me by Dept and period the ytd hours in the field labeled "YTDHours" One final glitch remains: I added one record for December (period 12) and you can see in "Query 1" that it is...

Bob: Thanks so much for taking the time to help. Yes, the query you wrote accomplishes part of the task I was looking for (actually Part A. in the new file I've attached called "Final_Result_with_Comments.xls") However, the second table I need is the Part B (also in file...

Hi, I attached a copy of the table hoping someone might take a few mintues to help (see Tbl_Hours_Actualz.xls). I'm running into trouble in ACCUMULATING data by dept for each month for the field "hours" I've also attached what I need ACCESS TO do which I've already accomplished using EXCEL...