Ignore empty form fields in query

[Bertie007]

Hey

I have a database which will contain a considerable amount of data, so I wanted to insert a search engine based on a query. Through a form the user would be able to search his database. The results would appear in a List Box.

If I take as criteria the form values. When I fill out the form and when I leave one field open, the query seeks for a record that contains that empty field.

Using the OR in criteria would result in too many results. I tried excluding empty fields but really don't seem to find the solution.

The user can search on ID (of the client), name, address, company , ...

Can somebody help me out?

Yours Sincerely

Bert Van Bergen

 

[scking]

Simply build a SQL Statement which looks at each control which will be used for the criteria and:

Dim strSQL As String
strSQL = "SELECT MySelectedValue FROM tblValues WHERE "

If Len(Me.txt1)>0 Then
strSQL = strSQL & "txt='" & Me.txt1 & "'"
End If

If Len(Me.txt2)>0 Then
strSQL = strSQL & ", txt='" & Me.txt2 & "'"
End If

strSQL = strSQL & ";"
Me.MyListbox.RecordSource = strSQL
Me.MyListbox.Requery

This is just a quick sample.

Steve King Growth follows a healthy professional curiosity

 

[Bertie007]

Problem solved!

Thank you very much!

Yours Sincerely

Bert Van Bergen