I have a split field search form called frmEmployeePaymentSearch which based on two tables: tblEmployees and tblEmployeeIntakeData.
The structure of the tblEmployees is: -
tblEmployee Table: -
Code: Select all
EmployeeID
EmployeeFirstName
EmployeeLastName
EmployeeName (Calculated Field: [EmployeeFirstName] & " " & [EmployeeLastName])
EmployeeAddress1
EmployeeAddress2
tblEmployeeIntakeData: -
Code: Select all
EmployeeWorkIngHRID
EmployeeID (foreign key)
EmployeePeriodStartDate
EmployeePeriodEndDate
EmployeePayRate
RegularHours
EmployeeDate
CustomerID (foreign key)
CustomerTips
I also have two unbounded text fields called txtStartPeriod and txtEndPeriod (in these two textboxes, the user will enter the date range). There is a also a command button called CmdSearch with an On Click event procedure with the following VBA code: -
Code: Select all
Private Sub CmdSearch_Click()
'Search button
Call Search
End Sub
Sub Search()
Dim strCriteria, task As String
Me.Refresh
If IsNull(Me.txtStartPeriod) Or IsNull(Me.txtEndPeriod) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Require"
Me.txtStartPeriod.SetFocus
Else
strCriteria = "([EmpPeriodStartDate] >= #" & Me.txtStartPeriod & "# And [EmpPeriodEndDate] <= #" & Me.txtEndPeriod & "#)"
task = "select * from tblEmployeeIntakeData where (" & strCriteria & ") order by [EmpPeriodEndDate] "
DoCmd.ApplyFilter task
End If
End Sub
I tried to add the following code to the strCriteria but I received an error
Code: Select all
" AND [EmployeeName] = """ & Me.txtEmployeeName & """
strCriteria = "([EmpPeriodStartDate] >= #" & Me.txtStartPeriod & "# And [EmpPeriodEndDate] <= #" & Me.txtEndPeriod & "#)"
task = "select * from tblEmployeeIntakeData where (" & strCriteria & ") order by [EmpPeriodEndDate] " AND [EmployeeName] = """ & Me.txtEmployeeName & """
DoCmd.ApplyFilter task
I need assistance with the VBA code to add to the command button cmdEmployeeNameSearch so that an user can search an Employee in the unbound texbox called txtEmployeeNameSearch and then filter between the employee's regular hours in the two unbound text boxes called wo txtStartPeriod and txtEndPeriod.
Thank you in advance for your help.