Changing Query Criteria (1 Viewer)

[Oreynolds]

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]

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]

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