Hi pretty new to access but slowly getting my head around it
I have an issue where I have two separate tables, one of these has line by line timesheet expenditure data for staff, the second table has the staff listed out with all their positions in the company, the band they were in for the position and the start and end dates for each role (so one person could have multiple roles with unique start and end dates)
I am running a query which has the critical information from timesheet expenditure but I am trying to formulate a Dlookup to pull off the Band they were in when each entry was made so between the Start and End Dates. The Band will then be used to work out a rate but one problem at a time!
Relevant Fields from tblCom_ExpenditureDownload:
Employee Name - Short Text
Expenditure Item Date - Date/Time
Relevant Fields from tblInfo_StaffDates
EmployeeName - Short Text
Band - Short Text (Contains Alphanumeric values)
Start Date - Date/Time
End Date - Date/Time
I have the following code
This gives me a result but it is the same value for all entries, and I am not sure where I am going wrong
Any help would be greatly appreciated
Rich
I have an issue where I have two separate tables, one of these has line by line timesheet expenditure data for staff, the second table has the staff listed out with all their positions in the company, the band they were in for the position and the start and end dates for each role (so one person could have multiple roles with unique start and end dates)
I am running a query which has the critical information from timesheet expenditure but I am trying to formulate a Dlookup to pull off the Band they were in when each entry was made so between the Start and End Dates. The Band will then be used to work out a rate but one problem at a time!
Relevant Fields from tblCom_ExpenditureDownload:
Employee Name - Short Text
Expenditure Item Date - Date/Time
Relevant Fields from tblInfo_StaffDates
EmployeeName - Short Text
Band - Short Text (Contains Alphanumeric values)
Start Date - Date/Time
End Date - Date/Time
I have the following code
Code:
Expr1: DLookUp("Band","tblInfo_StaffDates","[Employee Name] = '& [EmployeeName]&'" And "Expenditure Item Date >= [Start Date]" And "Expenditure Item Date <= [End Date]")
This gives me a result but it is the same value for all entries, and I am not sure where I am going wrong
Any help would be greatly appreciated
Rich