Maybe I did something incorrectly. I added the field to the
qryInternal
and
qryExternal as Mth:Month([Date]) and then added this field from the
qryExternal field list to the design grid of
qryTotalInternalandExternal
and
soretd ascending. I then tried to create a chart and same problem as
before,
it is looking at Mth as a text field and not a date and I still can't
get
the
date range box in the chart wizard. Did I do something wrong or leave
out
something?
Your help is REALLY appreciated! Thanks!
--
sistrlyn
:
You would need to add
Month([Date]) as Mth
to qryInternal and qryExternal so it can be used to sort in
qryTotalInternalandExternal.
--
Duane Hookom
MS Access MVP
--
message
The last SQL views I sent were with the suggestions you previously
sent
me.
Here are the original views:
(Re: Is your final query the actual Row Source property of the
chart?
Yes,
I am using the qryTotalInternalandExternal for the chart and I need
the
months to display by a fiscal date range Oct thru Sep.)
qryInternal
SELECT Count([tblCONCERNS/COMPLAINTS].[NC#]) AS [Internal Count],
Format([Date],"mmm") AS [Month], CTypeCodes.CT
FROM CTypeCodes INNER JOIN [tblCONCERNS/COMPLAINTS] ON
CTypeCodes.CTypeCode
= [tblCONCERNS/COMPLAINTS].CType
GROUP BY Format([Date],"mmm"), CTypeCodes.CT, DatePart("m",[Date])
HAVING (((CTypeCodes.CT)="internal"))
ORDER BY DatePart("m",[Date]);
qryExternal
SELECT Count([tblCONCERNS/COMPLAINTS].[NC#]) AS [External Count],
Format([Date],"mmm") AS [Month], CTypeCodes.CT
FROM CTypeCodes INNER JOIN [tblCONCERNS/COMPLAINTS] ON
CTypeCodes.CTypeCode
= [tblCONCERNS/COMPLAINTS].CType
GROUP BY Format([Date],"mmm"), CTypeCodes.CT, DatePart("m",[Date])
HAVING (((CTypeCodes.CT)="external"))
ORDER BY DatePart("m",[Date]);
qryTotalInternalandExternal
SELECT qryExternal.Month, qryInternal.[Internal Count] AS Internal,
qryExternal.[External Count] AS [External], ([Internal
Count]+[External
Count]) AS [Total NC#]
FROM qryExternal INNER JOIN qryInternal ON qryExternal.Month =
qryInternal.Month
GROUP BY qryExternal.Month, qryInternal.[Internal Count],
qryExternal.[External Count], ([Internal Count]+[External Count]);
--
sistrlyn
:
Your first post stated:
Month:Format([Date],"mmm") and
DatePart("m",[Date])
I don't see either of these expression in the three queries you
provided
to
us. Is you final query the actual Row Source property of the chart?
--
Duane Hookom
MS Access MVP
message
Sorry about the cap's.
Here are the SQL views of my queries:
*qryInternalMYMONTH
SELECT Count([tblCONCERNS/COMPLAINTS].[NC#]) AS [Internal Count],
Month([MyDate]) AS MyMonth, CTypeCodes.CT
FROM CTypeCodes INNER JOIN [tblCONCERNS/COMPLAINTS] ON
CTypeCodes.CTypeCode=[tblCONCERNS/COMPLAINTS].CType
GROUP BY Month([MyDate]), CTypeCodes.CT
HAVING (((CTypeCodes.CT)="internal"));
*qryExternalMYMONTH
SELECT Count([tblCONCERNS/COMPLAINTS].[NC#]) AS [External Count],
Month([MyDate]) AS MyMonth, CTypeCodes.CT
FROM CTypeCodes INNER JOIN [tblCONCERNS/COMPLAINTS] ON
CTypeCodes.CTypeCode
= [tblCONCERNS/COMPLAINTS].CType
GROUP BY Month([MyDate]), CTypeCodes.CT
HAVING (((CTypeCodes.CT)="external"));
*qryTotalInternalandExternalMYMONTH
(This is the query I am trying to use for charting)
SELECT qryExternalMyMonth.MyMonth, qryInternalMyMonth.[Internal
Count]
AS
Internal, qryExternalMyMonth.[External Count] AS [External],
([Internal
Count]+[External Count]) AS [Total NC#]
FROM qryExternalMyMonth INNER JOIN qryInternalMyMonth ON
qryExternalMyMonth.MyMonth = qryInternalMyMonth.MyMonth
GROUP BY qryExternalMyMonth.MyMonth, qryInternalMyMonth.[Internal
Count],
qryExternalMyMonth.[External Count], ([Internal Count]+[External
Count]);
Thanks for looking at this for me.
--
sistrlyn
:
Two suggestions:
1) don't reply with ALL CAPS. It is considered shouting.
2) provide the SQL views of your queries.
--
Duane Hookom
MS Access MVP
message
I TRIED WHAT YOU SUGGESTED AND I HAD THE SAME PROBLEM. I AM
UNABLE
TO
ACCESS
THE DATE RANGE FIELD IN THE CHART WIZARD BECAUSE IT DOES NOT
RECOGNIZE
"MYMONTH" AS A DATE...STILL SEES IT AS A TEXT DATA TYPE. ANY
MORE
SUGGESTIONS?
--
sistrlyn
:
Change "Month" to another name and change "Date" to another
name.
"Month"
and "Date" are reserved words. Also, Format produces a string
and
that
is
what you are seeing. You need to use MyMonth:Month([MyDate]).
--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com
in
message
I created a query where I used the expressions
Month:Format([Date],"mmm")
to
convert individual dates from my table and group them by
month.
In
the
same
query I used the DatePart("m",[Date]) to order them
chronologically
not
alphabetically. This query is used to create another query
where
I
am
using
the month field. I then am using this second query to chart
these
dates.
However, it removes the "Date" format from the month and
converts
it
to
text.
Therefore, I cannot chart the dates in the order I need
which
is
a
date
range
for the fiscal year. It charts the dates alphabetically.
Can
anyone
help
me?????
