none
Expression needed. RRS feed

  • Question

  • Table t01PAYE keeps Tax table that should be deducted from Employee salaries. Three fields "From" "To" and "Amount". 1000 records. If an employee earn R12 000 p.m. he will fall in the bracket "From" R11 994 "To" R12 014. "Amount"=R1100.

    Table t01Salaries have a field "MnthSal". I simply need an expression that searches t01PAYE for the correct Amount to be deducted. I opened a calculated field called PAYE in t01Salaries. The expression I am trying is =Dsum("[Amount]","t01PAYE","[from]<=[MnthSal] &" and [To]>=[MnthSal]")

    I guess my error is in the "where" part of the expression.

    Tuesday, April 12, 2016 11:56 PM

Answers

  • Table t01PAYE keeps Tax table that should be deducted from Employee salaries. Three fields "From" "To" and "Amount". 1000 records. If an employee earn R12 000 p.m. he will fall in the bracket "From" R11 994 "To" R12 014. "Amount"=R1100.

    Table t01Salaries have a field "MnthSal". I simply need an expression that searches t01PAYE for the correct Amount to be deducted. I opened a calculated field called PAYE in t01Salaries. The expression I am trying is =Dsum("[Amount]","t01PAYE","[from]<=[MnthSal] &" and [To]>=[MnthSal]")

    I guess my error is in the "where" part of the expression.

    You are trying to get this value in a query of table t01Salaries?  I believe you need to take the field reference [MnthSal] out of the quotes in the DSum expression:

        =Dsum("[Amount]","t01PAYE","[from]<=" & [MnthSal] & " and [To]>=" & [MnthSal])


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, April 13, 2016 3:36 AM

All replies

  • Table t01PAYE keeps Tax table that should be deducted from Employee salaries. Three fields "From" "To" and "Amount". 1000 records. If an employee earn R12 000 p.m. he will fall in the bracket "From" R11 994 "To" R12 014. "Amount"=R1100.

    Table t01Salaries have a field "MnthSal". I simply need an expression that searches t01PAYE for the correct Amount to be deducted. I opened a calculated field called PAYE in t01Salaries. The expression I am trying is =Dsum("[Amount]","t01PAYE","[from]<=[MnthSal] &" and [To]>=[MnthSal]")

    I guess my error is in the "where" part of the expression.

    You are trying to get this value in a query of table t01Salaries?  I believe you need to take the field reference [MnthSal] out of the quotes in the DSum expression:

        =Dsum("[Amount]","t01PAYE","[from]<=" & [MnthSal] & " and [To]>=" & [MnthSal])


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, April 13, 2016 3:36 AM
  • Thank you very much Dirk, it works.
    Wednesday, April 13, 2016 3:00 PM