Hi guys,

I can think of a few ways to achieve the desired results, but my question is more for opinions than a definitive answer.


What I have at the moment is a table, which I wish to be able to search through, and retrieve records from, based around 4 fields, namely; [First Name], [Surname], [Company], [Purchase Date].

At the moment I have a setup that looks like:

Where:
[XXX] = Text box
[>] = Toggle box
[^_] = List box

Now, what I want to be able to do, is for someone to type in, for example, a Surname and a Purchase Date, push both the appropriate toggle boxes, and the listbox update to show any results that match a:

The problem is, obviously, that these toggle boxes are dynamic, sometimes one might be active, other times all four might be, so the where statement would need to grow and shrink in relation to the toggle boxes.

Now I know I could set up a _Click() event for each toggle box, with all the different situations, and their layouts, but that's quite a lot of work, and is an exponential problem if, at a later date, more search criteria are required.


So, I'm basically wondering if anyone reading this can shed some light on how they'd approach it.

I know I could have a simple submit button setup, but I'm trying to expand my knowledge of this language.


Thanks in advance guys!

build a SQL statement based on the user input
use that sql statement to populate the list box (or combo box if preferred) manually
I don't think you need the 'toggle box', presume you mean check box. you can test if there is input in the text boxes and use those values

strWhereClause = " WHERE 1=1"
if strlen (txtFName) > 0 then strWhereClause = strWhereClause & " AND forename = '" & txtFname & "'"
if isdate (txtpurchasedate) then strWhereClause = strWhereClause & " AND purchasedate = " & cdate(txtpurchasedate)

the 1=1 means there's always a valid term int he where clause
text literals must be encapsualted with either ' or " to delimit the text
date literasl must be encapusalted with # and be in either ISO ("#yyyy/nn/dd#" or US format ("#mm/dd/yyyy#")

Got it sorted eventually, never thought to use:



Live and learn I guess.

Thanks for the help buddy.