Changing Query Criteria (1 Viewer)

Oreynolds

Member
Local time
Today, 22:11
Joined
Apr 11, 2020
Messages
157
Hi, I have a simple form with list box on showing data from a query that the user can filter by changing dates in two date boxes. In the query the criteria on the [SentDate] field is as follows which all works fine:

Between [Forms]![frmFindQuoteRecord]![txtStartDate] And [Forms]![frmFindQuoteRecord]![txtEndDate] Or Is Null

In the ideal world I need to make a change to the form which allows the user to easily filter those records that have been sent and those haven't. My plan was to provide and option group box with three check boxes:

1) ALL
2) Sent
3) Not Sent

To achieve this I would need to alter the criteria in the underlying query for the [SentDate] field as follows:

1) Between [Forms]![frmFindQuoteRecord]![txtStartDate] And [Forms]![frmFindQuoteRecord]![txtEndDate] Or Is Null
2) Between [Forms]![frmFindQuoteRecord]![txtStartDate] And [Forms]![frmFindQuoteRecord]![txtEndDate]
3) Is Null

I am struglleing to work out how I would achive this without possibly changing the data source for the list box to 3no. different queries? Does anyone have a more simple solution I could use?

Thanks
 

Minty

AWF VIP
Local time
Today, 22:11
Joined
Jul 26, 2013
Messages
10,330
You could make two SQL strings - one with the select SQL in it and one with a Where clause.
When you select the option code you select the appropriate where clause, add that to your Select clause then set the list rowsource to the combined SQL.

No need to store three queries, unless you want to.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:11
Joined
Feb 19, 2013
Messages
16,489
not well documented but assuming the listbox is a recordset and not a value list then you can reference the recordset filter property.

On my phone so can't provide more that but it would be something like

mylistbox.recordset.filter="whatever your filter is"

you would simplify your coding if you use an option group rather than three separate textboxes
 

Users who are viewing this thread

Top Bottom