PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, Sum(CheckBook.CheckAmount) AS
CountOfID, CheckBook.Date
FROM CheckBook
GROUP BY CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount, CheckBook.DepositAmount, CheckBook.Details,
CheckBook.Status, CheckBook.Date
HAVING (((CheckBook.Status)="no"));
On the other one, I tried with a code adapted from the one you made, but it
returns all status NO, regardless of the date.
Exactly. That's what you're asking for!!!!
You have Parameters defined... *but you're not using them*. Defining a
parameter just tells Access "I'm going to be asking for something and I want
it to be considered a date/time field when I do"; it doesn't change the query
unless you use it.
You say elsewhere in the thread:
I have two Date fields in the table. One showing the time of the
transaction called DateTime, another called Date with just date.
That's a MAJOR MISTAKE and completely unnecessary. What if your DateTime field
contained #3/5/2009 11:30:00#, and your Date field contained #12/25/2005#? One
of them's wrong; can you tell (based on the content of the record) which one
is wrong? You only need *one* field if you're only storing one point of time.
You can display the field twice, if you wish to see the date only in one case
and the time only (or the date and time) in another.
That said... try changing your query to
PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, Sum(CheckBook.CheckAmount) AS
CountOfID, CheckBook.Date
FROM CheckBook
GROUP BY CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount, CheckBook.DepositAmount, CheckBook.Details,
CheckBook.Status, CheckBook.Date
WHERE (((CheckBook.Status)="no"))
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND ChekcBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);
This query will probably not give you what you want though - since it's a
Totals query grouping by check number, name, date, etc. etc.; you'll see all
of these fields exactly once with its amount as the SumOfAmount and 1 as the
CountOfID. Since I don't clearly understand what you DO want it's hard to
suggest how to change the query. Assuming that you just want the sum of
amounts and the count of transactions for all checks in that date range, just
remove the fields you don't want to see:
PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT Count(*) AS CountOfID,
Sum(CheckBook.CheckAmount) AS SumOfAmount
FROM CheckBook
WHERE CheckBook.Status="no"
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND CheckBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);
If you just want to see all records with all fields within that date range,
without totals or counts, just don't use the Group By:
PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, CheckBook.Date
FROM CheckBook
WHERE CheckBook.Status="no"
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND CheckBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);
