Date Range Totals for Logical Fields

[Gary Walter]

sorry!!!!! WHERE clause goes before GROUP BY clause!!!!
queries 101!!!!

try...

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
WHERE
CDate(NZ(Q.[Lead Date],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#
GROUP BY
Q.[Lead Date],
Format$(Q.[Lead Date],'mmmm yyyy'),
Q.[Signed Up];

I don't see anything "wrong"...

Are you saying you copied the SQL
into SQL View of a *new query*, and
when you went into Design View it gave
you a syntax error.

Or, once you copied the SQL
into SQL View of a *new query*,
you could go into Design View,
but when you tried to save it, it gave
you a syntax error.

Did you just temporarily delete the
WHERE clause while in SQL View
to see if error went away -- so we
know error is in WHERE clause
(or in copy-pasting of SQL?).

If you copy the following to a new
query in SQL View, do you get an
error?


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
Q.[Lead Date] >= #1/28/2008#
And
Q.[Lead Date] < #2/2/2008#;

"Susan May"wrote:

Gary - got this message:

Syntax error (missing operator) in query expression

Q.[Signed Up] WHERE CDate(NZ(Q.[Lead Date],"1900-01-01")) >= #1/28/2008#
And CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;

Can you send me back the code with the missing operator? I can't figure
out
what's missing here.

Thanks a bunch!

oops...I forgot you have time values in [Lead Date]....

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],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;


note: last date criteria is midnight of "next day"
which will pick up those [Lead Date]'s of 2/1/2008
with a time value

(I hope that's all I goofed on)

good luck,

gary

 

[Susan May]

Wow Gary: I'm getting 2 error messages wanting me to enter Parameter value,
but I don't even see them listed in the query, they are:

DD2 List.Info Kit Date Sent - Enter Parameter Value
Q.Info Kit Sent

So, when I bypass this, there are not values that appear. I think we are
getting closer, but not there yet.

Again, I appreciate you helping me as I am no programmer and want to
streamline this reporting process.

sorry!!!!! WHERE clause goes before GROUP BY clause!!!!
queries 101!!!!

try...

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
WHERE
CDate(NZ(Q.[Lead Date],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#
GROUP BY
Q.[Lead Date],
Format$(Q.[Lead Date],'mmmm yyyy'),
Q.[Signed Up];

I don't see anything "wrong"...

Are you saying you copied the SQL
into SQL View of a *new query*, and
when you went into Design View it gave
you a syntax error.

Or, once you copied the SQL
into SQL View of a *new query*,
you could go into Design View,
but when you tried to save it, it gave
you a syntax error.

Did you just temporarily delete the
WHERE clause while in SQL View
to see if error went away -- so we
know error is in WHERE clause
(or in copy-pasting of SQL?).

If you copy the following to a new
query in SQL View, do you get an
error?


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
Q.[Lead Date] >= #1/28/2008#
And
Q.[Lead Date] < #2/2/2008#;

"Susan May"wrote:

Gary - got this message:

Syntax error (missing operator) in query expression

Q.[Signed Up] WHERE CDate(NZ(Q.[Lead Date],"1900-01-01")) >= #1/28/2008#
And CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;

Can you send me back the code with the missing operator? I can't figure
out
what's missing here.

Thanks a bunch!
:

oops...I forgot you have time values in [Lead Date]....

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],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;


note: last date criteria is midnight of "next day"
which will pick up those [Lead Date]'s of 2/1/2008
with a time value

(I hope that's all I goofed on)

good luck,

gary

 

[Gary Walter]

Hi Susan,

Please post the SQL for your query

[Qry_AllDDLeads by Date Range]

(i.e., go to SQL View of above query,
copy it and paste into post back to here)

thanks,

gary

Wow Gary: I'm getting 2 error messages wanting me to enter Parameter
value,
but I don't even see them listed in the query, they are:

DD2 List.Info Kit Date Sent - Enter Parameter Value
Q.Info Kit Sent

So, when I bypass this, there are not values that appear. I think we are
getting closer, but not there yet.

Again, I appreciate you helping me as I am no programmer and want to
streamline this reporting process.

sorry!!!!! WHERE clause goes before GROUP BY clause!!!!
queries 101!!!!

try...

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
WHERE
CDate(NZ(Q.[Lead Date],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#
GROUP BY
Q.[Lead Date],
Format$(Q.[Lead Date],'mmmm yyyy'),
Q.[Signed Up];

I don't see anything "wrong"...

Are you saying you copied the SQL
into SQL View of a *new query*, and
when you went into Design View it gave
you a syntax error.

Or, once you copied the SQL
into SQL View of a *new query*,
you could go into Design View,
but when you tried to save it, it gave
you a syntax error.

Did you just temporarily delete the
WHERE clause while in SQL View
to see if error went away -- so we
know error is in WHERE clause
(or in copy-pasting of SQL?).

If you copy the following to a new
query in SQL View, do you get an
error?


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
Q.[Lead Date] >= #1/28/2008#
And
Q.[Lead Date] < #2/2/2008#;

"Susan May"wrote:
Gary - got this message:

Syntax error (missing operator) in query expression

Q.[Signed Up] WHERE CDate(NZ(Q.[Lead Date],"1900-01-01")) >=
#1/28/2008#
And CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;

Can you send me back the code with the missing operator? I can't
figure
out
what's missing here.

Thanks a bunch!
:

oops...I forgot you have time values in [Lead Date]....

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],"1900-01-01")) >= #1/28/2008#
And
CDate(NZ(Q.[Lead Date],"9999-09-09"))< #2/2/2008#;


note: last date criteria is midnight of "next day"
which will pick up those [Lead Date]'s of 2/1/2008
with a time value

(I hope that's all I goofed on)

good luck,

gary