CODEWITHSUNDEEP
ms-accesscomboboxvbams-access-2010
james.sw.clark
james.sw.clark

Reputation: 357

Microsoft Access 2010: ComboBox text extraction / search

I have a text search in my form, which uses the following code to filter my table of staff data:

 Private Sub Command71_Click()

 DoCmd.ApplyFilter "", _

 "[Forename] Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'" & _

 "Or [Surname] Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'" & _

 "Or [ResearchArea]. Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'" & _

 "Or [Skills] Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'" & _

 "Or [EndDate] Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'"

 End Sub

The search works for all fields apart from [ResearchArea]. Both ResearchArea and Skills are ComboBoxes on my split form.

Both ResearchArea and Skills are fields in my Staff table.

New Skills can be added in the split form, but new ResearchArea's can only be added in the table.

The drop-down box on the Skills ComboBox contains repeated entries( e.g. if my listed skills on five staff are: "", "", "", "Accounting", "Accounting", then these options will appear in the drop-box), and blanks. I'd like it only to show unique entries, but also for me to be able to create new ones in this split form.

I'd also like to be able to search all staff's ResearchArea's, which I could do if they were text (like "Forename").

Upvotes: 0

Views: 80

Answers (1)

Gustav
Gustav

Reputation: 55856

You have a dot to remove on this line:

"Or [ResearchArea]. Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText] & "*'" & _

Aside, you cannot search dates - in a meaningful way - as you do.

Upvotes: 3

Related Questions