Calculating vacation time for current year per user

[danrahm]

I have a db containing three tables: User account, Time Cards, and Line items. The Time cards layout contains a portal to Line items in which users enter project hours and vacation time which is then summed to a calculation field (Time cards::Total PTO Vacation Hours) in Time cards for each user (Time cards::ID).

User account layout needs to calculate or sum the total vacation time (Time cards::Total PTO Vacation Hours) used for the current year (User account::PTO used this year_days) for each user (User account::ID).

The tables are related only by the ID fields. So everyone sees and edits there own data only. User account:Time cards is one to many and Time cards:Line items is many to many.

We started using this db in 2005 and all was well until 2006. The "User account::PTO used this year_days" field can not calculate based on data only for the current year. I need it to calculate how much vacation time a user has logged for the current year only.

I've attached a stripped down version of the db as a sample. With the current data entered in the sample, "User account::PTO used this year_days" should be only one (1) day, since only 8 hours of vacation time have been logged for 2006.

Any ideas on how I can go about fixing this? I've tried many things so far with no success.

2005-2006_Vacation_test.zip

[Walter B]

Consider adding an additional qualifier into your if statement for you PTO Vacation hour calculation. Something like if Year(Week Ending) = Year(Get(CurrentDate))

Or you could have a global that stores the current year.

Either way that should limit the sum to only the current year.

HTH

[danrahm]

I've actually tried both of those already and, to my surprise, neither worked. This is another reason I am so puzzled by this problem.