Question Query by date and checkbox

[Pat Hartman]

Did you try my original suggestion? You want to select rows where one of two conditions is true.
1. Between Start and End date
2. Cleared = False

This REQUIRES an OR operator.

Where SomeDate Between [StartDate] and [EndDate] OR ClearedFlg = False

Remember - if you didn't default the cleared flag to False, you may have null values and so you might have to deal with nulls.

If you write the expression this way - it REQUIRES parentheses to force Access to evaluate it correctly.

Where (SomeDate >= [StartDate] and SomeDate <= [EndDate]) OR (ClearedFlg = False OR ClearedFlg Is Null)

And Finally, if you need to be able to move the date range around so that it might be in the past, you have to worry about checks that were written AFTER the data range.

Where (SomeDate >= [StartDate] AND SomeDate <= [EndDate]) OR ((ClearedFlg = False OR ClearedFlg Is Null) AND SomeDate <= [EndDate])

 

[Mark_]

Mark, You gave me an idea, so I tried this on SDATE_AfterUpdate event

.RecordSource = "SELECT * FROM DATA WHERE " & _
    "(IsNull([StatD]) = True AND [Date Paid] <= #" & Me.SDATE & "#)"

Everything works perfectly. Thanks everyone for your help.

If you have it working, you may want to mark the thread as [SOLVED] so everyone knows.

P.S. also getting into a statement of cash flow in addition to the bank reconciliation?

 

[raziel3]

If you have it working, you may want to mark the thread as [SOLVED] so everyone knows.

P.S. also getting into a statement of cash flow in addition to the bank reconciliation?

Yep. That's the next report I want to tackle. Cash Flow for every quarter.