Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9

    DLookup expression challenge


    I'm trying to write a DLookup function based on an expression that changes.
    I have a table that contains someone's pay grade in the first column and then what their pay would be in the subsequent columns based on their years of service.

    I am having trouble using the proper format for the expression part of the function.

    For example, if I write =DLookup("[5]",... I get the correct pay amount for if they have 5 years of service. However, not every employee has the same years of service, so I am looking for a way to put criteria in for this function and everything I have tried has not worked.

    The Form is called "PayRate" and the field with the employee's years of service is called "Years"
    I have tried =DLookUp("=Forms![PayRate]![Years]",.... but instead of returning the correct pay, it just returns the number in years.
    i have tried other formats but I either get #Error or a blank field returned.

    Thanks to anyone who can help.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't like the table design, but try:

    =DLookUp(Forms![PayRate]![Years],....
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    I get the #Name? error

  4. #4
    cfwoodbury is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2012
    Posts
    11
    Can you provide the complete DLookup statement you are using?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Worked in a brief test, but I'll let cf take over.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    =DLookUp("=Forms![PayRate]![Years]","[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "'") - doesn't work, but if I do:
    =DLookUp("[5]","[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "'"), I get the proper dollar amount for someone with the grade and 5 years of service. Unfortunately, only for 5 years. Once I bring up the next employee, I get the wrong amount because they may have only 4 years or might have 10, etc.

  7. #7
    cfwoodbury is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2012
    Posts
    11
    It sounds like you are using a form to enter the pay Grade and Years and want to then lookup the BasePay from a table using the criteria of Grade and Years. And it appears you want to derive the BasePay on the form itself without using VBA. Is that correct? What is the name of your table?

  8. #8
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    That is correct.
    The name of the table that has the pay chart is called BasePay.
    The table with the list of the employees is called AllEmployees. This is the table the form is based on.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So you didn't try what I posted, deleting the quotes around the form reference and the =?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Redesign table BasePay!

    BasePay: BPID, Rate, Years, Amount

    Then your formula will be something like
    Code:
    =DLookUp("Amount","[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "' AND [Years] =  [Forms]![WorkedYears])

  11. #11
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    Quote Originally Posted by pbaldy View Post
    So you didn't try what I posted, deleting the quotes around the form reference and the =?
    I get "invalid Syntax" with that.

  12. #12
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    Ugh! There are 24 different pay grades with different rates from 1 year through about 40.

    One question, what does BPID represent?

  13. #13
    cfwoodbury is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2012
    Posts
    11
    It sounds like Base pay has field names (columns) that look something like this:

    Rate
    Grade
    Year1
    Year2
    .
    .
    .YearN

    And there must be only one record in your table at the moment because it is does not apply to a specific employee. It this is the case then I agree with Arvi that the best solution is to redo the table so that there is one record for each Rate/Grade combination.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by RJB View Post
    I get "invalid Syntax" with that.
    With what, exactly (show the code)?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    RJB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    9
    Yeah, I'm working on it.
    I actually have several hundred employees in the system already for other purposes with a unique EmployeeID.

    Right now, the Base Pay table has a column with the Grade, then a column for Year1, Year2, etc. The pay rates are under each year.

    We use the Access database to store Employee information, then an excel spreadsheet to calculate pay and then to a word document to type up various memos for our accounting department with the employee information and how much they are contracted to be paid for the duration of the project. I want to make life easier and just do it all in Access.

    We'll see how this works and I'll let you all know.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dlookup expression.
    By Perfac in forum Access
    Replies: 1
    Last Post: 02-04-2018, 01:34 AM
  2. Replies: 2
    Last Post: 05-27-2016, 06:10 PM
  3. Query Expression with IIF and DLOOKUP
    By bryan0 in forum Queries
    Replies: 7
    Last Post: 01-30-2014, 07:52 PM
  4. Complicated textbox expression challenge
    By Wombat in forum Access
    Replies: 2
    Last Post: 03-06-2012, 07:34 PM
  5. Dlookup Expression Help
    By chrismja in forum Queries
    Replies: 10
    Last Post: 10-29-2010, 03:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums