RE: counting and sorting dates specific
- From: Chris <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 16 Mar 2006 10:03:31 -0800
That wasn't the problem...but...I am giving up on the user having a calendar
to select the date. I'll just enter as you suggest and pass that to the
form, sorting order now works fine.
--
Thanks for your help, again,
Chris
"KARL DEWEY" wrote:
I do not know code or how you are using it..
Most times when Access prompts for something as in [SinceDate] the query or
code has not been satisfied. Could it be that you did not include the table
name - [tbl 1 CLIENT] - in you code with [SinceDate]?
"Chris" wrote:
I think I jumped the gun! I have further issues on this one.
I use a form for the user to input date ranges using a calendar. My "dates"
form calls the report and passes the date range for the query and for the
printout.
***pertinent code from "dates" form***
ElseIf Me.FormNum = 92 Then
stDocName = "rpt 100 ClientCountSourceDates"
strSQL = "[SinceDate] >= #" & CDate(Me!txtStartDate) & "# AND
[SinceDate] < 1+ #" & CDate(Me!txtEndDate) & "#"
stStatus = 0
Me.Visible = False
*** code end ***
Using your code suggested last, I modified to use the above range input as
follows:
*** sql ***
SELECT [tbl 1 CLIENT].Source, Count([tbl 1 CLIENT].Source) AS CountOfSource,
[tbl 1 CLIENT].Inactive
FROM [tbl 1 CLIENT]
WHERE ((([tbl 1 CLIENT].SinceDate) Between [Forms]![frm0
GetDates]![txtStartDate] And [Forms]![frm0 GetDates]![txtEndDate]))
GROUP BY [tbl 1 CLIENT].Source, [tbl 1 CLIENT].Inactive
HAVING ((([tbl 1 CLIENT].Inactive)=False))
ORDER BY Count([tbl 1 CLIENT].Source) DESC;
***sql end***
When I run the report, the "dates" form accepts date range and then I am
asked for [SinceDate]. The resulting report is either blank or all
[SinceDate]s.
Can you help me?
--
Thanks for your help,
Chris
"KARL DEWEY" wrote:
How about this --
SELECT [tbl 1 CLIENT].Source, Count([tbl 1 CLIENT].Source) AS CountOfSource,
[tbl 1 CLIENT].Inactive
FROM [tbl 1 CLIENT]
WHERE ((([tbl 1 CLIENT].SinceDate) Between [Enter start date] And [Enter end
date]))
GROUP BY [tbl 1 CLIENT].Source, [tbl 1 CLIENT].Inactive
HAVING ((([tbl 1 CLIENT].Inactive)=False))
ORDER BY Count([tbl 1 CLIENT].Source) DESC;
"Chris" wrote:
SELECT [tbl 1 CLIENT].Source, Count([tbl 1 CLIENT].Source) AS CountOfSource,
[tbl 1 CLIENT].Inactive
FROM [tbl 1 CLIENT]
GROUP BY [tbl 1 CLIENT].Source, [tbl 1 CLIENT].Inactive
HAVING ((([tbl 1 CLIENT].Inactive)=False))
ORDER BY Count([tbl 1 CLIENT].Source) DESC;
This is the successfull-for-all-dates version. My date field is
[SinceDate]. I want the user to select the date range.
--
Thanks for your help,
Chris
"KARL DEWEY" wrote:
Post your query SQL statement.
"Chris" wrote:
I'm having trouble getting a query to select, count AND sort properly when
specifying dates.
Fields are [Source], the client source in text format; [Since], the client
since date, in date format.
I am successful retrieving and sorting ALL records by [Source] and counting
them using a query that has column 1 as [Source] Group By and column 2 as
[Source] Count.
My problem comes in trying to select only certain dates using Between dates
feature of the query.
I am feeding a report with this. I've tried selecting dates first in a
separate query, last before report in separate query, in a single query,
tried the report sort and order by features...but, I am lost can't find my
way out.
Can someone please push me in the correct direction?
--
Thanks for your help,
Chris
- References:
- RE: counting and sorting dates specific
- From: Chris
- RE: counting and sorting dates specific
- From: KARL DEWEY
- RE: counting and sorting dates specific
- Prev by Date: Re: please check the syntax of this query
- Next by Date: Re: All users from one tbl not in another tbl
- Previous by thread: RE: counting and sorting dates specific
- Next by thread: which type of query
- Index(es):
Relevant Pages
|
