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

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

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.