search/filter form

[antonyx]

ok i would like to create a very specific search form. the form has many fields but they all belong to one simple table.. Enquiries.

i know this table isnt normalised but this section is separate from the database and i need this certain filter feature on the enquiry form i have made.

a screen of the form can be found here.

http://bb.1asphost.com/antonyx6/enqform.jpg

the top 3 combos contain all the records for that particular field.. the first bit of info i need is how to alter the statement for the combo lists to ignore values with no entries..this is because not every stored enquiry will contain an enquirer's forname.. is this correct for my Forname combo....?

SELECT zEnquiries.Enquiry_ID, zEnquiries.Enq_Forname FROM zEnquiries WHERE zEnquiries.Enq_Forname is NOT NULL;

i will take things one step at a time.. so if i can clear this up first i will describe what i want to happen next if thats ok..

thank you.

 

[Ethereal]

for a search form perhaps you would want to make it similar to the form in that screen shot but instead of text boxes you would have combo boxes... Their rowsources would contain the values from the corresponding fields using the query builder. However you do not want to make such search criteria that cannot exist so all of the rowsources would use IIF functions as their "like" clauses... Example: Like IIf(IsNull(cboExample), "*", cboExample)
This would search for all records if there was no criteria selected (using the wildcard) and would search for records containing values like the one selected in cboExample otherwise.. This makes probably no sense, but i hope you get something out of it, or that I am helping you with what i think i am ?

 

[antonyx]

ok i see what you mean, here is my problem tho
i used this code and my not nulls dont show in the list.

SELECT Enquiry_ID,Enq_Forname FROM zEnquiries WHERE Trim(Enq_Forname & '')<>''

here is the tricky part.
because enquiries of this sort are not as clear cut as bookings, it is impossible to know what details our customers will give us on the phone.. they may or may not leave a name.. a phone no... or a pickup and destination respectively, and our job is to find out what price we quoted to the customer using this form.

taking this into consideration this is what i want.

the form loads with all the combos listing their not null values..i can then select any combo choice..

eg. if i chose john from the forname combo, then all the other combos will change their respective fields that belong to only records with the forname john.. this way, my user can one by one (depending on what information they receive from the customer first) select the details they hear, and the combos narrow down the potential records...

would this be an afterupdate event i would need to add to each combo list??