Duane
Below are the two queries. After I select the dates in the 3 combo boxes I
receive the following error message: "There is an error executing the
command" when running from the switchboard. If I try and open the report
from
the database window, I get prompted for 1 date and receive the following
error message: "This expression is typed incorrectly, or it is too complex
to
be evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts of
the expression to variables. "
How should the queries look when I get finished?
Where do I set the column headings expession to: ColHead:"W" &
DateDiff("ww",CalDate], [Forms]![Switchboard]![FirstDate]) ?
---1st Select Query - QryMonthCurr
PARAMETERS [Forms]![Switchboard]![FirstDate] DateTime;
SELECT TabOfferings.CalDate, TabOfferings.Amount, TabOfferingType.Type,
MonthName(Month([caldate])) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON TabOfferingType.Type =
TabOfferings.Type
WHERE (((MonthName(Month([caldate])))=[Forms]![Switchboard]![FirstDate]));
---2nd Select Query - QryMonthPrev1
PARAMETERS [Forms]![Switchboard]![SelectMonth] DateTime;
SELECT TabOfferings.CalDate, TabOfferings.Amount, TabOfferingType.Type,
MonthName(Month([caldate])) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON TabOfferingType.Type =
TabOfferings.Type
WHERE
(((MonthName(Month([caldate])))=[Forms]![Switchboard]![SecondDate]));
---3rd Select Query - QryMonthPrev2
PARAMETERS [Forms]![Switchboard]![SelectMonth] DateTime;
SELECT TabOfferings.CalDate, TabOfferings.Amount, TabOfferingType.Type,
MonthName(Month([caldate])) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON TabOfferingType.Type =
TabOfferings.Type
WHERE (((MonthName(Month([caldate])))=[Forms]![Switchboard]![ThirdDate]));
--1st CrossTab Query - QryNewCrosstabCurr
TRANSFORM Sum(QryMonthCurr.Amount) AS SumOfAmount
SELECT QryMonthCurr.Type
FROM QryMonthCurr
GROUP BY QryMonthCurr.Type
ORDER BY QryMonthCurr.Type, QryMonthCurr.CalDate
PIVOT QryMonthCurr.CalDate In ("W0","W1","W2","w3","w4","w5");
--2nd CrossTab Query - QryNewCrosstabPrev1
TRANSFORM Sum(QryMonthPrev1.Amount) AS SumOfAmount
SELECT QryMonthPrev1.Type
FROM QryMonthPrev1
GROUP BY QryMonthPrev1.Type
ORDER BY QryMonthPrev1.Type, QryMonthPrev1.CalDate
PIVOT QryMonthPrev1.CalDate In ("W0","W1","W2","w3","w4","w5");
--3rd CrossTab Query - QryNewCrosstabPrev2
TRANSFORM Sum(QryMonthPrev2.Amount) AS SumOfAmount
SELECT QryMonthPrev2.Type
FROM QryMonthPrev2
GROUP BY QryMonthPrev2.Type
ORDER BY QryMonthPrev2.Type, QryMonthPrev2.CalDate
PIVOT QryMonthPrev2.CalDate In ("W0","W1","W2","w3","w4","w5");
I tried this query since I could not figure out how I was going to get the
whole month with the date selection you were showing me:
PARAMETERS [Forms]![Switchboard]![FirstDate] DateTime;
SELECT TabOfferings.CalDate, TabOfferings.Amount, TabOfferingType.Type,
Month([caldate]) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON TabOfferingType.Type =
TabOfferings.Type
WHERE (((Month([caldate]))=Month([Forms]![Switchboard]![FirstDate])));
When I run the crosstab I still get the message:"This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables."
John
Duane Hookom said:
You use the "ColHead:"W" & DateDiff("ww",[CalDate..." as the column in
your
query grid that generates the column headings.
If you follow my instructions, you can use the crosstab as the record
source
of a subreport.
Just post the SQL view of your best attempt at the solution.
--
Duane Hookom
MS Access MVP
Duane
Where do I set the ColHead: field below up? I think I set the "W0",W1"
etc
up in the ColumnHeadings in the crosstab query properties.
Can I use the query for the source of the subreport or should I set up
a
separate report with the crosstab query as the sorce?
If I try and run a report with the crosstab as the source, I received
the
followng message after enter 1/1/05 for the prompt: "This expression is
typed
incorrectly, or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try simplifying
the
expression by assigning parts of the expression to variables. "
Is there anyway to send this database to you?
John
:
The switchboard form must be open when running the query/report.
I would use relative dates for column headings. For instance if you
had a
date value in a text box "txtEndDate" on your form, you could set the
column
headings expession to:
ColHead:"W" & DateDiff("ww",[CalDate],
[Forms]![Switchboard]![TxtEndDate])
Set the Query->Parameter
[Forms]![Switchboard]![TxtEndDate] DateTime
Set the column Headings property to:
"W0","W1","W2","W3","W4"
--
Duane Hookom
MS Access MVP
Duane
If I run the crosstab query from the database window, it prompts
once
as
[Forms]![Switchboard]![SelectMonth] and works fine.
PARAMETERS [Forms]![Switchboard]![SelectMonth] Text ( 255 );
SELECT TabOfferings.CalDate, TabOfferings.Amount,
TabOfferingType.Type,
MonthName(Month([caldate])) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON TabOfferingType.Type
=
TabOfferings.Type
WHERE
(((MonthName(Month([caldate])))=[Forms]![Switchboard]![SelectMonth]));
When I run the report it prompts twice showing
[Forms]![Switchboard]![SelectMonth] in the title then the following
error
comes up: "You can't use a pass-through query or an non-fixed-column
crosstab
query as a record source for a subform or subreport. Before you bind
the
subform or subreport to a crosstab query, set the query's
ColumnHeadings
peoperty."
After reading help, I have to put the exact field names in the
ColumnHeadings field. How can I run it for a selected month. The
output
titles for July example would be "Type", "7/3/2005", "7/10/2005",
"7/17/2005", "7/24/2005", "7/31/2005" but for August would be
"Type",
"8/7/2005", "8/14/2005", "8/21/2005", "8/28/2005".
John
:
Change the parameter type to "Text".
--
Duane Hookom
MS Access MVP
Duane
I am using the monthname of the month, a string instead of the
number
of
the
month which would be an integer. Here is what the query says with
the
parameter:
Select Query - QryMonthCurr
SELECT TabOfferings.CalDate, TabOfferings.Amount,
TabOfferingType.Type,
MonthName(Month([caldate])) AS [Month]
FROM TabOfferingType INNER JOIN TabOfferings ON
TabOfferingType.Type
=
TabOfferings.Type
WHERE
(((MonthName(Month([caldate])))=[Forms]![Switchboard]![SelectMonth]));
Crosstab Query
TRANSFORM Sum(QryMonthCurr.Amount) AS SumOfAmount
SELECT QryMonthCurr.Type
FROM QryMonthCurr
GROUP BY QryMonthCurr.Type
ORDER BY QryMonthCurr.Type, QryMonthCurr.CalDate
PIVOT QryMonthCurr.CalDate;
John
:
This question gets asked and answered here quite often. You must
enter
the
parameter data types into the Query->Parameters:
Forms!SwitchBoard!SelectMonth Integer
--
Duane Hookom
MS Access MVP
Duane
I have tried using a from combo box for this and I get the
following
error
message: "The Microsoft Jet database engine does not recognize
'Forms!SwitchBoard!SelectMonth' as a valid field name or
expression". I
have
used the "Build" function in the query to find the field on
the
form
instead
of typing it in myself.
John
:
If you have a control on a form "Forms!frmDates!txtDate", you
might
be
able
to replace "Now" with "Forms!frmDates!txtDate".
However, you haven't shown us your SQL view of the crosstabs.
--
Duane Hookom
MS Access MVP
--
I have an unbound report with 3 subreports. The source for
all
the
subreports
are crosstab queries. The data for the crosstab queries are
3
select
queries.
If I put month(now), month(now)-1, and month(now)-2 in the
