Search Form

[Gorphus]

I have been asked to write a database for my company that stores customer information. I have the database completed, but would like to streamline it better. FYI there are only about 3 people that will have change access to the database, the rest will only be able to view information. The way that I have it set up now is off of the switchboard links to about 14 reports for viewing of data. Since the switchboard will only allow you to have 8 entries per page, users are having to change pages to get to other searches. Also this does not allow the users to search by multiple fields, only the ones that I created. What I would like to do is have a Form, with all of the fields from my 4 tables and allow the user to select/input multiple or single criteria to search with. Designing the Form is not the problem, the main thing that I am not understanding how to do is setup the query to accept null values, but still show that information on the report/form/whatever. Let me know if I am not clear and I will try and clarify.

Thanks,

Jonathan

 

[JCollins]

I would try to create an option group ("OR" and "AND".

In some event (after-update maybe), you can use a select case to change the Filter....
'You will need to do this several times for each option group button you have selected.

Hope this helps.

ie.

dim strFilter as string

Select Case optGroupA

Case 1 '"AND"
strFilter = "(([field1Name]= [forms]![yourForm]![txBox1]) "
strFilter=strFilter & "And ([field2Name]= [forms]![yourForm]![txBox2]))"

Case 2 '"OR"
strFilter = "(([field1Name]= [forms]![yourForm]![txBox1]) "
strFilter=strFilter & "And ([forms]![yourForm]![txBox2] is null))"

End Select

DoCmd.ApplyFilter , strfilter

 

[LQ]

Check out this thread....
http://www.access-programmers.co.uk/ubb/Forum4/HTML/004007.html

I was able to adapt this to my needs. You also might look into using Querydef. Here is the Knowledgebase article on it.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q136062