Results 1 to 7 of 7

Thread: Dlookup Problem

  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Dlookup Problem

    I have two tables, one contains an employees start date and the other list the the Pay Period Start Date and End Date. I would like to pull the pay period number based upon the employees hire date. Pay Period Number depends on the date of which the employees hire date falls. I have created the following expression but it only give me the first pay period number for every record:

    Test: DLookUp("[Pay Period]","Master Pay Period Number"," [Date Of Hire] between # " & "[Start Date]" & "#" And "#" & "[End Date]" & "#")

    What am I doing wrong?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what error message are you getting, and where is the error reported

    offhand I think think its going to be "[Start Date]"

    IO think you could shortcircuit some of the string concatentation going on there
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2008
    Posts
    3
    On my Pay Period Master Table the fields are:

    Pay Period number , Start Date, End Date
    Example would be"
    1 , 1/1/08 1/14/08

    Om my Employee Master Table I have the hire date
    I would like to pull pay period number based upon the employees hire date. So if someone was hired 1/3/08 it would give me pay period 1.

    I am not getting any errors, but the results are pay period 1 irregardless of the employess hire date. So if someone started 2/1/08 it still results in pay period 1.

    Thanks

  4. #4
    Join Date
    May 2008
    Posts
    15
    Could you use DatePart("ww",[Date of Hire],wsMonday,firstJan).
    This should give you the week no. 0-52, if I'm understanding you right, this should work?

  5. #5
    Join Date
    May 2008
    Posts
    3

    Reply

    My Employee Table contains the hire date
    I have a second table that has beg date, end date, and pay period number.

    So 1/1/08 - 1/14/08 would be period 1, there are 26 pay period with start and end dates.
    I would like to write an expression that determines which pay period number corresponds to an employees hire date. I have written the following but it returns pay period number 1 for all instances irregardless of employees start date.

    Test: DLookUp("[Pay Period]","Master Pay Period Number"," [Date Of Hire] between # " & "[Start Date]" & "#" And "#" & "[End Date]" & "#")

  6. #6
    Join Date
    May 2008
    Posts
    15
    In some versions of SQL, WHERE Between does not find the search terms supplied in the search criteria? i.e. Between '1' AND '5' would not return fields of '1' and '5' but would return '2;3;4'. Not certain how Access handles the between.

    Does each have the hire date the same as Start Date?

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Instead of

    [Date Of Hire] between # " & "[Start Date]" & "#" And "#" & "[End Date]" & "#"

    try

    [Date Of Hire] Between #" & [Start Date] & "# And #" & [End Date] & "#"

    imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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