Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Dlookup question - Between dates?

    Hello,

    I'm having another dlookup issue...not sure how to get this one. What I have is a table (tbl_EmployeeTable) with fields (EmployeeName, Q Number, Q Num Start Date, Q Num End Date) and a form (frm_Packer) with fields OrigPacker, ScanDate).

    I use this and it reurns the first name, but I want it to return the name where field [ScanDate] is between [Q Num Start Date] and [Q Num End Date]

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "'")

    Here is what I tried, and it gave "The expression you entered has an invalid date value."
    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "'" And [Forms]![frm_Packer]![ScanDate] Between #" & [tbl_EmployeeTable]![Q Num Start Date] & #" And "# & [tbl_EmployeeTable]![Q Num End Date] & "#")

    Does anyone know what I am doing wrong?

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    I realized I was formatting fields in my table as date so I deleted that and this takes, but returns a #Name error.

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "'" And "# & [Forms]![frm_Packer]![ScanDate] & #" Between [Q Num Start Date] And [Q Num End Date])

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "' And [Q Num Start Date] <= #" & [Forms]![frm_Packer]![ScanDate] & "# AND [Q Num End Date] >= #" & [Forms]![frm_Packer]![ScanDate] & "#")
    Paul

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pbaldy View Post
    Try

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "' And [Q Num Start Date] <= #" & [Forms]![frm_Packer]![ScanDate] & "# AND [Q Num End Date] >= #" & [Forms]![frm_Packer]![ScanDate] & "#")
    This sort of works...the only problem is, if an employee is still working here, the field [Q Num End Date] will be blank, so if there is no end date the return value is blank.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    So add

    OR [Q Num End Date] Is Null

    to the end date test and surround those 2 in parentheses.
    Paul

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pbaldy View Post
    So add

    OR [Q Num End Date] Is Null

    to the end date test and surround those 2 in parentheses.
    Did you mean like this? I probably don't understand, cuz this doesn't work....

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "' And [Q Num Start Date] <= #" & [Forms]![frm_Packer]![ScanDate] & "# AND ([Q Num End Date] >= #" & [Forms]![frm_Packer]![ScanDate] & "#")OR ([Q Num End Date] Is Null))

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "' And [Q Num Start Date] <= #" & [Forms]![frm_Packer]![ScanDate] & "# AND ([Q Num End Date] >= #" & [Forms]![frm_Packer]![ScanDate] & "# OR [Q Num End Date] Is Null)")
    Paul

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pbaldy View Post
    Try

    =DLookUp("[EmployeeName]","[tbl_EmployeeTable]","[Q Number] = '" & [Forms]![frm_Packer]![OrigPacker] & "' And [Q Num Start Date] <= #" & [Forms]![frm_Packer]![ScanDate] & "# AND ([Q Num End Date] >= #" & [Forms]![frm_Packer]![ScanDate] & "# OR [Q Num End Date] Is Null)")
    Perfect.

    Thank you so much!!

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, glad we got it sorted out.
    Paul

Posting Permissions

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