Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: DMax ... I think ... Please Help

    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:

    Dmax((Date()-[DateHired]>=[DaysOfService]),"EmployeeTable Query","")

    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.

    Any help is appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Check the syntax of the DMax() function, it is:
    Code:
    DMax(<ColumnName>, <TableName>, <Condition>)
    With <Condition> being optional. You placed the condition in the first parameter.
    Have a nice day!

Tags for this Thread

Posting Permissions

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