Solved Where string

[ClaraBarton]

How do I select all records with a boolean of 0
AND all records within a date range of boolean both 0 and -1?
In other words all transactions that were not previously cleared in addition to cleared and uncleared of this month.

 

[The_Doc_Man]

In SQL, the constant FALSE is equivalent to a Boolean of zero. I don't quite understand the second sentence but here's a guess...

SELECT A, B, C, BooleanD, DateE
FROM MyTable
WHERE (NOT BooleanD) OR (DateE BETWEEN LowDate AND HighDate) ;

Or ... WHERE (BooleanD = FALSE) OR ...

That's the SELECT clause. You could do the UPDATE or DELETE equivalent using the same FROM/WHERE setup.

 

[tvanstiphout]

Select * from tblTransactions where Cleared = False or TransactionDate >= DateSerial(Year(Date), Month(Date), 1);

 

[Gasman]

Could even have NOT Cleared?

 

[ClaraBarton]

Private Sub btnReconcile_Click()
        Dim strWhere                As String
        Dim cl                      As Boolean
        Dim strDateRange            As String

strWhere = "fAccountID = " & Me.Parent.txtAccountID
strWhere = strWhere & " AND Cleared = False"
strDateRange = GetDateRange("tblTransactions.CkDate", 59)
strWhere = strWhere & " Or " & strDateRange
Debug.Print strWhere
    DoCmd.OpenForm "popReconcile", _
        WindowMode:=acDialog, _
        WhereCondition:=strWhere
       
End Sub

Returns:

fAccountID = 47 AND Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#

Returns ALL dates! 20,500

 

[Isaac]

don't you need some brackets?

fAccountID = 47 AND (Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#)

 

[DHookom]

You will return all dates where Cleared is False. If that is not what you want, you may need to replace the Or with And.

 

[ClaraBarton]

Oh Right! Where's my head? I'm getting All Accounts that aren't cleared. Else it's right. Thank you.

 

[Gasman]

Private Sub btnReconcile_Click()
        Dim strWhere                As String
        Dim cl                      As Boolean
        Dim strDateRange            As String

strWhere = "fAccountID = " & Me.Parent.txtAccountID
strWhere = strWhere & " AND Cleared = False"
strDateRange = GetDateRange("tblTransactions.CkDate", 59)
strWhere = strWhere & " Or " & strDateRange
Debug.Print strWhere
    DoCmd.OpenForm "popReconcile", _
        WindowMode:=acDialog, _
        WhereCondition:=strWhere
      
End Sub

Returns:

fAccountID = 47 AND Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#

Returns ALL dates! 20,500

Use brackets to define your logic.

 

[Mark_]

Something to keep in the back of your head. When you have multiple criteria you can also create a set of matching queries to UNION.

Query 1 returns all transactions with Cleared = False
Query 2 returns all transactions between a date range.
Query 3 returns all transactions for client who has an outstanding balance
Query 4 returns all transactions that contain a specific item or set of items
Query 5 returns all transactions that are being sent out of country
Query 6 returns all transactions for clients who have blue hair

This normally comes up when you have a relatively large number of different criteria and you want to selectively use them.

 

[ClaraBarton]

Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026# AND fAccountID = 47
Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026# Or fAccountID = 47
Cleared = False Or (tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) Or fAccountID = 47

None of these work

 

[Gasman]

Cleared = False Or (tblTransactions.CkDate Between #1/1/2026# And #2/25/2026# AND fAccountID = 47)

Even

Cleared = False Or ((tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) AND (fAccountID = 47))

 

[ClaraBarton]

fAccountID = 47 AND (Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#)

Doesn't limit dates or account

 

[ClaraBarton]

Cleared = False OR ((tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) AND (fAccountID = 47))
Cleared = False AND ((tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) AND (fAccountID = 47))

The first doesn't limit date
The second doesn't return cleared withing date

 

[Isaac]

fAccountID = 47 AND (Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#)

Doesn't limit dates or account

I thought that's what you wanted - either cleared to be false, or the tran date to be specific range?

 

[Gasman]

Cleared = False OR ((tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) AND (fAccountID = 47))
Cleared = False AND ((tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#) AND (fAccountID = 47))

The first doesn't limit date
The second doesn't return cleared withing date

You need to understand your logic and the difference between AND and OR.
The first I posted should give you ALL not cleared (regardless of date or account) and also anything (cleared or not) between those dates and from account 47.
The second should do the same, but a little more explicit.

Build one criteria. Surround that with brackets and then add the next.

This is what you said you wanted?

In other words all transactions that were not previously cleared in addition to cleared and uncleared of this month.

, though you did not specify a certain account.

 

[cheekybuddha]

fAccountID = 47 AND (Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#)

Doesn't limit dates or account

Are you sure this doesn't work?

You should see only:
transactions from account = 47
uncleared transactions from any time
both cleared and uncleared transactions within the date range.

Do you have any other criteria?

What is your whole SQL in the end?

 

[ClaraBarton]

@cheekybuddha. That's exactly what I want.

fAccountID = 47 AND (Cleared = False Or tblTransactions.CkDate Between #1/1/2026# And #2/25/2026#)

Doesn't limit the account...
It's the WhereCondition on form open.
the form recordset is"

SELECT qryCkTransaction.TransactionID, qryCkTransaction.fAccountID, qryCkTransaction.AccountName, qryCkTransaction.CkDate, tblNames.FullName, qryCkTransaction.Cleared, qryCkTransaction.Debit, qryCkTransaction.Credit
FROM tblNames RIGHT JOIN qryCkTransaction ON tblNames.NameID = qryCkTransaction.fNameID
ORDER BY qryCkTransaction.CkDate;

 

[ClaraBarton]

This form has been working on account and date but I was not getting the uncleared from before the date. Adding the cleared and uncleared messes it.

 

[cheekybuddha]

Try the following queries in the query builder to see if they produce different results:

SELECT
  t.TransactionID,
  t.fAccountID,
  t.AccountName,
  t.CkDate,
  n.FullName,
  t.Cleared,
  t.Debit,
  t.Credit
FROM qryCkTransaction t
LEFT JOIN tblNames n
       ON n.NameID = t.fNameID
WHERE t.fAccountID = 47
  AND (
    t.Cleared = False
    OR
    t.CkDate BETWEEN #1/1/2026# AND #2/25/2026#
  )
ORDER BY
  t.CkDate;

SELECT
  a.*
FROM (
  SELECT
    t.TransactionID,
    t.fAccountID,
    t.AccountName,
    t.CkDate,
    n.FullName,
    t.Cleared,
    t.Debit,
    t.Credit
  FROM qryCkTransaction t
  LEFT JOIN tblNames n
         ON n.NameID = t.fNameID
  WHERE t.fAccountID = 47
) a
WHERE a.Cleared = False
   OR a.CkDate BETWEEN #1/1/2026# AND #2/25/2026#
ORDER BY
  a.CkDate;