Multiple Crtieria search forms

[cynaptic]

I am trying to create a form that will accept multiple search criteria using the following SQL statement. However I am not sure of the SQl or how to create the form. Any help/tips/divine intervention appreciated.

The idea is that the form will select records on one or more of the parameters below.

SELECT *
FROM CompanyContacts
WHERE Sector = Forms![SearchForm]![Sector]
OR BusinessManager = Forms![SearchForm]![BusinessManager]
OR BusinessSector = Forms![SearchForm]![BusinessSector]
OR LECArea = Forms![SearchForm]![LECArea]
OR NumberOfEmployees = Forms![SearchForm]![NumberOfEmployees]
OR Turnover = Forms![SearchForm]![Turnover]
OR CompanyStatus = Forms![SearchForm]![CompanyStatus]
OR CreativeEdinburgh = Forms![SearchForm]![CreativeEdinburgh]
OR Softacad = Forms![SearchForm]![Softacad]
OR Partner = Forms![SearchForm]![Partner]
OR InnovationRating = Forms![SearchForm]![InnovationRating]
OR Allocated = Forms![SearchForm]![Allocated]
OR SEELStatus = Forms![SearchForm]![SEELStatus]
ORDER BY CompanyName;

Thanks

 

[specv]

Build your select in a string :

dim mySelect as string
mySelect = "SELECT * FROM CompanyContacts WHERE "

if Forms![SearchForm]![BusinessManager] <> &quot;&quot; then
mySelect = mySelect & BusinessManager = Forms![SearchForm]![BusinessManager]
endif

if Forms![SearchForm]![BusinessManager] <> &quot;&quot; then
mySelect = mySelect & &quot; OR &quot; & BusinessManager = Forms![SearchForm]![BusinessManager]
endif

...

mySelect = mySelect & &quot; ORDER BY CompanyName&quot;
(you will have to add condition to check if you put the &quot;OR&quot; and for the &quot;WHERE&quot; if there are condition or not)

And after execute the statement.

Phil

 

[cynaptic]

Philipe thanks for this

If I understand correctly this is building an sql statement on the basis of a set of IF statements, please could you explain the process a bit. I am not sure I understand.

sorry to be so slow but am only on first coffee.

Alex

 

[specv]

Exactly, you build your select in a string and after you can use this string on the rowsource of a list, listview, datagrid... to show your results. The rowsource property is a string containing a sql statement.

Phil