criteria

C

Christina

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
GROUP BY TblClients.Method;
the dates I type are 1/1/08 end 1/31/08
In the table I have seven records, and on eis dated Feb 2008.

Method CountOfID
Outreach 3
WalkIn 4
 
J

John W. Vinson

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
GROUP BY TblClients.Method;
the dates I type are 1/1/08 end 1/31/08
In the table I have seven records, and on eis dated Feb 2008.

Method CountOfID
Outreach 3
WalkIn 4

As noted elsewhere in this thread (I hope my messages are getting through!):
you need to actually *use* the criteria (in a WHERE clause), not just declare
them:

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE [Date] >= [Forms]![dialog]![Beginning Date]
AND [Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date])
GROUP BY TblClients.Method;
 
C

Christina

I dont know much. Please explain to me what you mean by actually use the
criteria in a where clause. I create the query with the ID, which is to be
counted, and the Method, which it is to grouped by. I then put the code as
instructed in the SQL view. What do I do next. Please in detail

Thanks

John W. Vinson said:
PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
GROUP BY TblClients.Method;
the dates I type are 1/1/08 end 1/31/08
In the table I have seven records, and on eis dated Feb 2008.

Method CountOfID
Outreach 3
WalkIn 4

As noted elsewhere in this thread (I hope my messages are getting through!):
you need to actually *use* the criteria (in a WHERE clause), not just declare
them:

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE [Date] >= [Forms]![dialog]![Beginning Date]
AND [Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date])
GROUP BY TblClients.Method;
 
C

Christina

I used this
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]);
But it brings data outside the date range. I am looking for records with
status No inside a date range.

I am not expert, so please bear with me.

Thanks


John W. Vinson said:
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]);
 
J

John W. Vinson

I dont know much. Please explain to me what you mean by actually use the
criteria in a where clause. I create the query with the ID, which is to be
counted, and the Method, which it is to grouped by. I then put the code as
instructed in the SQL view. What do I do next. Please in detail


Create a new query.
Don't put ANYTHING in any of the fields and don't select a table.
Select View... SQL. You should see a big blank textbox with the word SELECT;
in the upper left corner.

Copy and paste

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE [Date] >= [Forms]![dialog]![Beginning Date]
AND [Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date])
GROUP BY TblClients.Method;

over the SELECT.

Change to datasheet view to see if it is giving you the right results (you'll
get prompts for the beginnning date and end date if the form isn't open),
and/or to query design view to see what this looks like in the design window.
 
C

Christina

Thank you so much for your patience. It worked well for both projects.

THANKS!!!!!!

John W. Vinson said:
I dont know much. Please explain to me what you mean by actually use the
criteria in a where clause. I create the query with the ID, which is to be
counted, and the Method, which it is to grouped by. I then put the code as
instructed in the SQL view. What do I do next. Please in detail


Create a new query.
Don't put ANYTHING in any of the fields and don't select a table.
Select View... SQL. You should see a big blank textbox with the word SELECT;
in the upper left corner.

Copy and paste

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE [Date] >= [Forms]![dialog]![Beginning Date]
AND [Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date])
GROUP BY TblClients.Method;

over the SELECT.

Change to datasheet view to see if it is giving you the right results (you'll
get prompts for the beginnning date and end date if the form isn't open),
and/or to query design view to see what this looks like in the design window.
 
C

Christina

Thank you so much for your patience. It worked well.

Thanks

John W. Vinson said:
I dont know much. Please explain to me what you mean by actually use the
criteria in a where clause. I create the query with the ID, which is to be
counted, and the Method, which it is to grouped by. I then put the code as
instructed in the SQL view. What do I do next. Please in detail


Create a new query.
Don't put ANYTHING in any of the fields and don't select a table.
Select View... SQL. You should see a big blank textbox with the word SELECT;
in the upper left corner.

Copy and paste

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE [Date] >= [Forms]![dialog]![Beginning Date]
AND [Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date])
GROUP BY TblClients.Method;

over the SELECT.

Change to datasheet view to see if it is giving you the right results (you'll
get prompts for the beginnning date and end date if the form isn't open),
and/or to query design view to see what this looks like in the design window.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top