Multiple Parameters on same field query design

[JoysTick]

I have a query with the Field [FirstVisitDate]. I want to drive this query from a form and have created an unbound Field on my form called [StartDate].

In my query design, I can easily put something like > Forms![MyForm]![StartDate] to return all records with a [FirstVisitDate] greater than the value entered in [StartDate]. However I don't just want a static value here and I want to be able to choose values say less than the date selected or more than the date selected or indeed all records regardless of the value in [Startdate].

I've created an unbound combo box field on my form with a ValueList RowSource of
1;New Clients;2;Pre-Existing Clients;3;All Clients;

So if:
1 is selected I only want records with a [FirstVisitDate] that is greater than the value on my form in [StartDate]
2 is selected I only want records with a [FirstVisitDate] that is less than than the value on my form in [StartDate]
3 is selected I want all records returned.

How can I string all of this together or am I going about this the wrong way. So many ways to skin the cat...

 

[jwindon]

This requires a little code, but the easiest way to get the "New Clients" or the "Pre-Existing Clients" would be two separate queries.

Then before your action that runs either of these, you will have to check the value of your combo box to decide which query to run or to open the table (All Clients).

HTH

Another cat could be one query that has references to two unbound text boxes. Toggle the visibility of the text boxes on the AfterUpdate event of the combo. If it is "All Clients", run a command to open the table.

[This message has been edited by jwindon (edited 11-06-2001).]

 

[JoysTick]

THanks JWindon,
I think the second option would be better as I've already got lots of code hanging off the query.

Can you be a bit more specific how this would work? How would ech of the combos be referenced in the QBE pane?

TX

 

[jwindon]

Does your combo derive its data from any other source? Or is it simply a value list?

In the case of the latter...

The query pane would read

FirstVisitDate

Criteria: =>Forms![frmSearchForm]![StartDate_1]

OR

<Forms![frmSearchForm]![StartDate_2]

When the user selects from the combo, use your code to set the opposite [StartDate] to the same value so that it is not empty.

Give that a try. Sorry I could not test it.