Creating a search function

Okay i have a table created in Access with these fields ...

Cno
Date
Kgms
Amount
Destination
Origin

I am making a search form for it ..The problem is i can just get a better way to creat a search function ..The way i am creating it is just to lenghty ..This is what i am doing

if ( cno And Date And Kgms And Amount And Destination And ORigin are not empty )
then

use the Select SQL statement Where Cno is this blah blah

else if ( cno And Date And Kgms And Amount And Destination is not empty but Origin is )
Then

use SQL statement where ...

U get the idea right i have to right too many if else ....How can i get rid of that

I've written these types of things in ASP before, which possibly isn't what you're doing. Maybe they'll help you anyway, the principles can still be used:

Code:

---

dim strSQL,
    WhereClause,
    ItemValue

strSQL = "SELECT * FROM tblData "
'
'Method 1.  Grab each form element individually
'
ItemValue = Request.Form("Field1")
if ItemValue <> "" Then
  WhereClause = WhereClause & " AND Field1 = '" & ItemValue & "'"
End If

ItemValue = Request.Form("Field2")
if ItemValue <> "" Then
  WhereClause = WhereClause & " AND Field2 = '" & ItemValue & "'"
End If

'
' Method 2.  Loop the array.
'
For Each Item In Request.Form
  ThisItemName  = Request.Form.Key (Item)
  ThisItemValue = Request.Form.Item(Item)
  WhereClause = WhereClause & " AND " & ThisItemName & " = '" & ThisItemValue & "'"
Next

If WhereClause <> "" Then
  strSQL = strSQL & " WHERE " & WhereClause & " ;"
End If
strSQL = strSQL & " ;"

---

Watch out for bad input data corrupting your SQL string though ;)