Please, read my comment to the question.
On the first look your query looks OK, but.. i suggest to:
brackets around field names,
2) add parameters
3) check value passed to query through DateTimePicker control.
PARAMETERS [dateFrom] DATE, [dateTo] DATE;
SELECT LedgersName as [Ledgers Name], TransDate as [Date], Total as [Total], TotalPaid as [Total Paid], Balence as [Balence], Notes as [Notes], LedgersID as [Ledgers ID], TransID as [Trans ID]
WHERE (Notes='Checked In' OR Notes='Checked Out') AND TransDate Between [dateFrom] And [dateTo];
Then call this query using OleDbCommand
] together with OleDbParameters.AddWithValue
Be aware, don't use List of reserved words in Access 2002 and in later versions of Access