Dear all,
I made a Search Form in which users can select all kinds of different options regarding software systems (for example: software type, size, modules, ...).
On the bottom of the form they can hit a button to run a query. This query compares the form with my table and gives matching records. In this way my users are able to look for specific software systems.
However, in my table, some of my fields have multiple options (modules can be: financing AND hr AND accounting, ...). But in the form they can only select 1 option (financing OR hr OR accounting, ...).
How can I make sure that my query doesn't give an empty result when they choose module financing, but in the table financing is never alone.
In other words, if users look for "financing" my query now only looks for records that only have "financing". I want the situation to be that if users look for "financing" the query sees "this record has financing and accounting, so i can show this record".
Atm I always get an empty table when running my query ..
SELECT Software.[Software name], Software.[Customer Suitability], Software.Modules, Software.[Mobile Capabilities], Software.[System Hosting], Software.[Additional Product Info], Software.[Licence policy], Software.[Global vs local], Software.[Generic vs sector specific], Software.[Core vs add-on]
FROM Software
WHERE (((Software.[Software name])=Forms!SEARCH_Software![Software name] Or Forms!SEARCH_Software![Software name] Is Null) And ((Software.[Global vs local])=Forms!SEARCH_Software!globalvlocal Or Forms!SEARCH_Software!globalvlocal Is Null) And ((Software.[Generic vs sector specific])=Forms!SEARCH_Software!genericvsectorspecific Or Forms!SEARCH_Software!genericvsectorspecific Is Null) And ((Software.[Core vs add-on])=Forms!SEARCH_Software!corevaddon Or Forms!SEARCH_Software!corevaddon Is Null) And ((Software.[Software type])=Forms!SEARCH_Software![Software Type] Or Forms!SEARCH_Software![Software Type] Is Null) And ((Software.[Customer Suitability].Value)=Forms!SEARCH_Software![Customer Suitability] Or Forms!SEARCH_Software![Customer Suitability] Is Null) And ((Software.Modules.Value)=Forms!SEARCH_Software!Mo dules Or Forms!SEARCH_Software!Modules Is Null) And ((Software.[Mobile Capabilities].Value)=Forms!SEARCH_Software![Mobile capabilities] Or Forms!SEARCH_Software![Mobile capabilities] Is Null) And ((Software.[System Hosting].Value)=Forms!SEARCH_Software![System hosting] Or Forms!SEARCH_Software![System hosting] Is Null) And ((Software.[Additional Product Info].Value)=Forms!SEARCH_Software![Additional Product Info] Or Forms!SEARCH_Software![Additional Product Info] Is Null) And ((Software.[Licence policy].Value)=Forms!SEARCH_Software![Licence policy] Or Forms!SEARCH_Software![Licence policy] Is Null));
Thanks in advance!