Multi Field search form

[Rob Ross]

I need help creating a multi field search form. I have a database that tracks help desk tickets and the only thing I am lacking is a way to search for records based on multiple fields in the database. Most of the fields use combo boxes to fill the data based on other tables in the database since these fields get used quite a lot. I am trying to create a search form that can do this using combo boxes for some of the fields for easy selection as well as text searches of other fields but also ignore any fields that are not populated so that I can the create a report. Does anyone have any examples of such a form?

 

[pbaldy]

The sample db here demonstrates one method:

http://www.baldyweb.com/BuildSQL.htm

If there's only a couple of search fields, this might be easier:

http://access.mvps.org/access/queries/qry0001.htm

It gets really messy looking with more than a couple of fields though.

 

[oblivion86]

Hi,

I am facing a somewhat similar problem.

I would like to perform queries on multiple fields in a single table.
i.e. I would like to search for male(gender - 1 field) caucasians(race - 1 field) from a single table and return a report displaying a picture of that person.

I understand that I have to use [forms]![photosearch]![gender] as well as [forms]![photosearch]![race] in my query.

The query will be successful whenever I run a single field query, but whenever I include more than one field, it will return nil results.

Hope you can help.
Thanks!

 

[pbaldy]

You likely need to put the second criteria on the second criteria line. That creates an "OR" comparison instead of an "AND" comparison.

 

[oblivion86]

Hi Paul,

Thanks for the quick reply.

I think what I want the query to perform would be an "AND" comparison.

Let's say I would like to search for males who are Chinese.
Correct me if I am wrong, wouldn't that be Male AND Chinese?

By the way, if i use the wildcard "*" function, I have no problems searching multiple fields in the form and getting the correct results.

The problem only occurs when I use the "=" function.

Hope u can help. THanks!

 

[pbaldy]

Yes, it would be AND. What is your SQL?

 

[oblivion86]

Hi Paul,

I am rather new at this so I do not understand what you mean by SQL.

I got this far without coding a single line of code.

I basically had a table of data (race, gender, company) made a search form and got stuck at the query results when implementing the =[forms]![photosearch]![race] etc.

 

[pbaldy]

When you have your query in design view, switch to SQL view (method depends on version, but it should be fairly obvious). That's SQL. The query design grid is just a user interface to create SQL.

 

[oblivion86]

Ic.

SQL listed below.

Is it an issue with leaving some of the fields blank when I only want certain criteria? e.g. Males and Chinese, while leaving company, street and report fields blank, will leaving those fields blank cause some error to my query results?

SELECT Database.Gender, Database.Race, Database.Company, Database.Street, Database.Report
FROM [Database]
WHERE (((Database.Gender)=[Forms]![SearchF]![Gender]) AND ((Database.Race)=[Forms]![SearchF]![Race]) AND ((Database.Company)=[Forms]![SearchF]![Company]) AND ((Database.Street)=[Forms]![SearchF]![Street]) AND ((Database.Report)=[Forms]![SearchF]![Report]));

 

[pbaldy]

Yes it will. Here's one technique:

http://access.mvps.org/access/queries/qry0001.htm

 

[oblivion86]

Hi Paul,

Sorry I have been away for a while. I noticed that if I make use of drop down menus (combo boxes) for the search forms, the query works great.

However, when I want to search by free text, e.g. an employee ID (primary key), the query results would return everything.

How should I construct the query for free text search? Do I use the [Like "*" & [Forms].[Form1].[Text0] & "*"] method?

 

[oblivion86]

Hi,

Anyone got any idea how do i fix the query for free text fields? (e.g. address where I want to search by a street name, or an employee ID where I can try to search for the first 3 or 4 digits of the employee ID)

Do i use the Like operator mentioned in the above post?

Thanks!

 

[pbaldy]

Yes, you use Like and wildcards, as in your example, but with delimiters:

Like "'*" & [Forms].[Form1].[Text0] & "*'"

 

[oblivion86]

Thanks. But if I just require the wildcard to be for the rear of the employee ID/address, do I remove the asterisk at the end?

Employee ID:
J201201
I want to search for employee IDs beginning with J2012, does that mean I should query it this way: J2012*, therefore removing the initial asterisk in Like "'*" & [Forms].[Form1].[Text0] & "*'" ??

Or does it work itself out?

Thanks.

 

[pbaldy]

No, you need to take it out.

 

[oblivion86]

alright thanks!

 

[oblivion86]

Hi,

If I use the "Like" wildcard query method, do I have to write it this way?

Like "*" & [forms]![form1]![street] & "*" OR [forms]![form1]![street] Is Null

or just the initial part will do?

Another question would be how do I include a date picker option when I am creating a date field in the search form? (i.e. I would like to use a date picker to facilitate the input of the date in the search form)