Search a split field form by name and between a date range

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Search a split field form by name and between a date range

Post by wire_jp »

Hello,

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
The structure of the tblEmployeeIntakeData:

tblEmployeeIntakeData: -

Code: Select all

EmployeeWorkIngHRID
EmployeeID (foreign key)
EmployeePeriodStartDate
EmployeePeriodEndDate
EmployeePayRate
RegularHours
EmployeeDate
CustomerID  (foreign key)
CustomerTips
In the a split field search form called frmEmployeePaymentSearch, there is a unbounded text box called txtEmployeeNameSearch and a command button called cmdEmployeeNameSearch.
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
Up until this point, this VBA code works fine to filter the employee's regular hours between a date range

I tried to add the following code to the strCriteria but I received an error

Code: Select all

 " AND [EmployeeName] = """ & Me.txtEmployeeName & """
when this code is added to the strCriteria, I received an error message

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

You have to add the extra condition to strCriteria. By appending it to task, it comes after the ORDER BY clause instead of being part of the WHERE clause.

Code: Select all

    strCriteria = "[EmpPeriodStartDate] >= #" & Me.txtStartPeriod & "# And [EmpPeriodEndDate] <= #" & _
        Me.txtEndPeriod & "#) AND [EmployeeName] = """ & Me.txtEmployeeName & """
    task = "select * from tblEmployeeIntakeData where (" & strCriteria & ") order by [EmpPeriodEndDate]"
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

Thank you very much for your prompt response and your explanation. I applied the changes, but the result shows a list of different employees' hours between the specified date range. However, when I enter an employee name in the txtEmployeeNameSearch, it results in a pop-up parameter box, where the user has to enter an Employee Name and afterwards the results are filtered for all of the employees between the specified date range (it is not filtering for the specific employee entered in the textbox called txtEmployeeNameSearch). I would like to avoid using a pop-up parameter box. I would the user to enter the Employee Name in the txtEmployeeNameSearch and then filter between the employee's regular hours in the two unbound text boxes called wo txtStartPeriod and txtEndPeriod (without a parameter box appearing to enter the Employee Name). How can this be done?

Thank you in advance.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

Does this work?

Code: Select all

    strCriteria = "[EmpPeriodStartDate] >= #" & Me.txtStartPeriod & "# And [EmpPeriodEndDate] <= #" & _
        Me.txtEndPeriod & "#) AND [EmployeeName] = """ & Me.txtEmployeeNameSearch & """"
    task = "select * from tblEmployeeIntakeData where (" & strCriteria & ") order by [EmpPeriodEndDate]"
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

Thank you for your response. I reviewed your code and I saw that you made a change from Me.txtEmployeeName to Me.txtEmployeeNameSearch. I modified the VBA code with this change but an Employee Name parameter value box pops up and when I enter the Employee Name, it shows all of the employees within the specified date range (i.e. it do not filter to show the specified employee within the specified date range).So the answer to your question is that it does not work, as expected.

I checked the Record Source of the split field form and it was set to "EmployeeID" from the table called tblEmployeIntakeData and I changed it to "EmpIntakeID" from the table tblEmployeIntakeData.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

Does the table contain a field named EmployeeName ?
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

No, the table do not contain a field named EmployeeName. It contains a field called EmployeeID (foreign key), derived from the table tblEmployees

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

You cannot filter on a field not in the record source of the form.

Create a query based on tblEmployeeIntakeData and tblEmployees, joined on EmployeeID.
Add all fields from tblEmployeeIntakeData to the query grid, plus the EmployeeName field from tblEmployees.
Save the query and set the Record Source of the form to this query.
Use the query name in the code too:

Code: Select all

    strCriteria = "[EmpPeriodStartDate] >= #" & Me.txtStartPeriod & "# And [EmpPeriodEndDate] <= #" & _
        Me.txtEndPeriod & "#) AND [EmployeeName] = """ & Me.txtEmployeeNameSearch & """"
    task = "SELECT * FROM [queryname] WHERE " & strCriteria & " ORDER BY [EmpPeriodEndDate]"
Replace queryname with the name you gave the query.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

Thanks you for your prompt response. I followed your instructions and the split field form is now working fine to produce the desired results after entering the Employee Name, the Start Period Date and the End Period Date in the text boxes. The only other issue, is that when I click the Clear command button (i.e. CmdClear) and the Show All command button (i.e. CmdShowAll), the Employee Name field data appears as "#Name?". How can you fix this issue?

Thank you in advance for your help.

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

I found another issue: if I close the split field form and I re-open it, the the Employee Name field data appears as "#Name?". If I switch from Form View to Design View and then switch back to the Form View, the Employee Name field now shows the employees' names (instead of "#Name?")

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

That's hard to investigate without seeing a copy of the database...
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

Thank you for the update. The issue is due to the foreign keys. I did not mention before that I have another table tblEmployeePayRates, which is linked to the tblEmployeeIntakeData table in my query called qryEmployee Payments:. The structure of the tblEmployeePayRates
<code>
EmployeePayRateID
EmployeePayRate
EmployeeHourlyTime
</code>

EmployeePayRateID field from the tblEmployeePayRates table is joined to the tblEmployeeIntakeData as a foreign key.

When I open the frmEmployeePaymentSearch (i.e. the split field form), the fields which are the foreign keys e.g. EmployeeName, Employee Pay Rate all show the field data with the "#Name?"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

I repeat my previous reply... :sorry:
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans, thank you for the update. Any chance of sharing a copy of the database with you? I do not see an attachment link in the portal.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

If you click 'Full Editor and Preview' below this thread, you can then drag a zip file containing the database into the reply area. Max file size is 250 KB.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Search a split field form by name and between a date range

Post by wire_jp »

Hi Hans,

Thank you for the information! However, I was able to resolve the issue. I checked the VBA code for the Show All command button and Clear command button and I realized that they were still referencing the tblEmployeeIntakeData. Thus I had to change the reference from the table called tblEmployeeIntakeData to the query called qryEmployeePayments.

Thank you again for all of your help!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search a split field form by name and between a date range

Post by HansV »

Glad to hear that you were able to resolve it!
Best wishes,
Hans