NEW ACCESS USER HERE ... DIVING IN TO SELF TEACH ... NEED SOME EXPERT HELP.
I am creating a Vacation / PTO file. I have 4 tables (Employee, Accrual Rates, Approver, VacPTOLog). I have created a Query using the Employee table and the Accrual Rates table. The tables are linked based on a text field called HourlySalaried (appears in both tables).
In ther query, I have created a calculated field for LengthOfService:Date()-[DateHired]. I need this field to filter the query based on DaysOfService in the Accrual Rate Table.
The Accrual Rate Table contains data such as Type (Vacation , PTO, Floating, Bereavement), Days of Service (#Years*365), Days Earned Per Month, Days Earned Per Year and Maximum Days Available. I have different accrual rates for Salaried vs. Hourly staff. The data is static.
Based on an Employees Length of Service, I need the data to be filtered based on the highest available accrual rate for an individuals length of service. For example, if I am employed for 5 years with X Company, I have an accrual for the first year, a new rate for the 2 year, and a new rate beginning the 5th year. I want my query to return only the data for the 5th year as I have surpassed years 1 and 2. I used the following formula in the Criteria field for DaysOfService:
The formula results in Access crashing. I have tried on multiple PC's - same result. When I use Date()-[DateHired]>=[DaysOfService], the Query returns Accrual Rates for years 1, 2 and 5. 1 and 2 no longer are valid as the Emp-loyee is now earning at the year 5 rate.