counting and sorting dates specific

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 said:
Post your query SQL statement.

Chris said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
Post your query SQL statement.

Chris said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much, Karl, that did the trick! And I think I understand it!
--
Thanks for your help,
Chris


KARL DEWEY said:
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 said:
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 said:
Post your query SQL statement.

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Post your query SQL statement.

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

Post your query SQL statement.

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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;


:

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


:

Post your query SQL statement.

:

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?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Date Sorting in Crosstab 3
Sorted and Unique Date Period 2
Combining Date queries 1
Dcount returning no results!!! 0
minimize repeating query criteria & generating monthly YTD totals 1
Between date with greater than or eaqual to 4
Access Dcount (multiple criteria) 3
Joining 3 Queries That Count 8

Back
Top