Using Where clause in Macro (ApplyFilter) for Search Form

  • Thread starter Thread starter hikaru
  • Start date Start date
H

hikaru

I am using a macro (ApplyFilter) on a button to search in the database for
records the match the search details entered.

I wrote in:
Filter Name: select * from Out
Where Condition: ([Out]![year]=[Forms]![Out_Search_3]![combo_year] And
[Out]![Out_num]=[Forms]![Out_Search_3]![txt_Out_num]) Or
([Out]![year]=[Forms]![Out_Search_3]![combo_year] Or
[Out]![Out_num]=[Forms]![Out_Search_3]![txt_Out_num])

I have a search combobox (combo_year) with yeas from (2001-2015) and a
search text (txt_Out_num) for the user to enter any number (ID) to search for
its record.

what I want is to get the search result as following:
to match both fields together if both entered
or
to match only the the entered field if one field only entered

also the in the search text of the Number (ID), it should get all IDs the
has number 2 in them if number 2 was entered in the search field for example.
to explain, if the database has IDs like (2, 255 and 5) it should show in the
results of both records of (2 and 255) because both have number 2 in them.

i hope you understand my problem.
any help is appreciated, thanks.
 
Try this for the Where argument:

([Out]![year]=[Forms]![Out_Search_3]![combo_year] And
[Out]![Out_num]=[Forms]![Out_Search_3]![txt_Out_num]) Or
([Out]![year]=[Forms]![Out_Search_3]![combo_year] Or
([Out]![Out_num]Like [Forms]![Out_Search_3]![txt_Out_num] & "*"
And [Forms]![Out_Search_3]![txt_Out_num] Is Not Null))
 
Back
Top