Hi Susan,
I'm sorry if I was not clear, but the test query
SELECT
[Lead Date],
TypeName([Lead Date]) As ActualType
FROM
[Qry_AllDDLeads by Date Range];
was meant to be a stand-alone query to
verify the actual type of the query field
[Qry_AllDDLeads by Date Range].[Lead Date]
my guess was that result of the above query
would show "String" in the ActualType column
based on how the filtering was (not) working for you.
You have provided the SQL for the query
*based on* [Qry_AllDDLeads by Date Range],
but it may help if you provide SQL for this
base query as well.
Are you setting a format of [Lead Date] in the
column "Properties" of either query?
I wonder if that is what you meant by:
" I just change the structure of the date field to a short date
and that didn't make the query run any different."
Again...any time you *format* a pure Date/Time,
it becomes a STRING (and so will sort/filter as
a string, instead of as a Date/Time).
///////////////////////////////////////////
Another thing that may be at first hard to understand
designing a GROUP BY query in the grid ...
"I put the where clause under the Group by Lead Date"
Field: [Lead Date]
Table: [Qry_AllDDLeads by Date Range]
Total: GROUP BY
Sort:
Show:
Criteria: BETWEEN #1/28/2008# AND #2/1/2008#
or:
That will produce a HAVING clause. That was not
why the "dates" were filtering as strings, but you will
learn that HAVING can be inefficient because it
does the filtering only after all the groups have been
made and the aggregating has occurred.
To create a WHERE clause, you would need to
add [Lead Date] a second time to grid,
change the GROUP BY to WHERE,
and put your filter dates in that column's
Criteria row (not under original GROUP BY column):
Field: [Lead Date]
Table: [Qry_AllDDLeads by Date Range]
Total: WHERE
Sort:
Show:
Criteria: BETWEEN #1/28/2008# AND #2/1/2008#
or:
the above filtering will happen *before* groups
are created and aggregation occurs.
Sometimes the difference between applying the filter
before grouping (WHERE) and applying the filter
after grouping (HAVING) will also effect the results
you get for your aggregate(s) like SUM(...).
///////////////////////////
Another note if you are using Access 2000 or later,
"DISTINCTROW" really is designed for queries
that involve more than one "table source."
Plus, in a GROUP BY query, your "group" should do the
work of creating distinct rows.
/////////////////////////////
if you are tired and frustrated, sometimes a "little success"
can help you out of the "gumption traps."
if our multiple guesses were correct (you have somehow formatted
[Lead Date] into a string, you might copy the following into a new
query just to see some possible "success."
SELECT
Q.[Lead Date] AS LeadDate,
Format$(Q.[Lead Date],'mmmm yyyy') AS LeadDateByMonth,
Q.[Signed Up] AS SignedUp,
Sum(Q.[Active Recruit]) AS SumOfActiveRecruit,
Sum(Q.[Info Kit Sent]) AS SumOfInfoKitSent,
Sum(Q.[Direct Recruit]) AS SumOfDirectRecruit,
Sum(Q.[Initial Contact Made]) AS SumOfInitialContactMade,
Sum(Q.[DD Invite Letter Sent]) AS SumOfDDInviteLetterSent,
Sum(Q.[Accepted DD Invite]) AS SumOfAcceptedDDInvite,
Sum(Q.[Attended DD Mtg]) AS SumOfAttendedDDMtg,
Sum(Q.[Follow-up After DD Mtg]) AS SumOfFollowUpAfterDDMtg,
Sum(Q.[Has Contract]) AS SumOfHasContract,
Count(*) AS LeadCnt
FROM [Qry_AllDDLeads by Date Range] AS Q
GROUP BY
Q.[Lead Date],
Format$(Q.[Lead Date],'mmmm yyyy'),
Q.[Signed Up]
WHERE
CDate(NZ(Q.[Lead Date],#9/9/9999#))
Between
#1/28/2008#
And
#2/1/2008#;
It (very inefficiently) changes [Lead Date] back to a Date/Time
before applying date range in WHERE clause.
If I have not done something stupid (my typing sucks...),
I'd be interested in the results you get....
good luck,
gary
Susan May said:
I don't know much about select clauses and subqueries, so I tried
putting this in
Select[Lead Date]), TypeName([Lead Date]) As ActualType From
[Qry_AllDDLeads by Date Range] for [Lead Date] between
#2/4/2008# and #2/8/2008#) and got the error message
"Check the subquery's syntax and enclose the subquery in
