Search by one field or more field

[mavisyew]

Hi All,

I have a form to allow the user to search by paratmeter.

1. Date
2. Status
3. Type

My question will be, the user can enter either one field to search for the list of information or enter the 3 fields and those which totally match the 3 field will show.

For example in my table:

Date	Status	Type
29/08/2014	Pending	Toilet
01/10/2014	New	Toilet
08/08/2014	resolved	Carpark
29/08/2014	Pending	Door

If the user search for Status = Pending only then it will show:

29/08/2014

Pending

Toilet

29/08/2014

Pending

Door

If the user Search by Type = Toilet, and Status = New then it will show:

01/10/2014

New

Toilet

If the user Search by Date = 29/08/2014, Type = Toilet, and Status = Pending then it will show:

Date	Status	Type
29/08/2014	Pending	Toilet

How can i write the query?

I wrote something below but dont not work.
It show me all the data.

SELECT TBLCaseDetail.Status, TBLCaseDetail.ReportedDate, TBLCaseDetail.Type
FROM TBLCaseDetail
WHERE (((TBLCaseDetail.Status)=Forms!FormFindCase!Status Selection) Or ((TBLCaseDetail.ReportedDate)=Forms!FormFindCase!D ateSelection) Or ((TBLCaseDetail.Type)=Forms!FormFindCase!CaseTypeS election)) or (((TBLCaseDetail.Status)=Forms!FormFindCase!Status Selection) And ((TBLCaseDetail.ReportedDate)=Forms!FormFindCase!D ateSelection)) ;


Thanks for helping

[mavisyew]

If the user enter only one field it will search using that field only.
If the user enter all the 3 fields or only 2 fields, then it will use the field which enter to search for the case list.

Thanks

[ItsMe]

I use VBA to create an SQL string. Then, the SQL string will be used to query the tables. There are various approaches to this. But, building the string using VBA behind a form is the key point.
http://www.baldyweb.com/BuildSQL.htm

http://allenbrowne.com/ser-62code.html

[mavisyew]

Hi ItsMe,

Thanks for your reply.
Have read your code.
Not really understand as i am quite new to access.

[orange]

Here's a free video tutorial showing a simple Search form. It's in Access 2000/2003 but the concept/content is key to your issue.

Here is a link from Allen Browne that applies to your situation.
Good luck