Data type mismatch due to ColumnHeadings in crosstab query

[Brian]

Here is the basic output of the crosstab query. The column headings comes
from a DateSerial(Year(XXX),Month(XXX),1) to get totals by month:

ID 10/1/2005 11/1/2005 12/1/2005 -> etc. through 12 months total
1115 359.216 78.901 12.789
1789 243.894 123.400 597.189
1934 12.774 1549.271 9.781

I tried setting column headings for my crosstab query as:
"ID", "Month1", "Month2" etc. -> "Month12"
but that gives me a "Data type mismatch in criteria expression".

I also tried just "Month1", "Month2" etc. -> "Month12" to see if it was the
inclusion of the first column (my RowHeading column) that was causing the
problem, but with the same error.

 

[Brian]

Clarification: I can see what the problem is, but I don't know how to get the
result I want. The ColumnHeadings property must use data from the data values
from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve months from
today, but how do I refer to each month generically so that I can refer to
them in the report.

Today, the first column is 10/1/2005, but when I run this report next a
month, it will have a different name: 11/1/2005. I am missing something here.

 

[Dirk Goldgar]

Clarification: I can see what the problem is, but I don't know how to
get the result I want. The ColumnHeadings property must use data from
the data values from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve
months from today, but how do I refer to each month generically so
that I can refer to them in the report.

Today, the first column is 10/1/2005, but when I run this report next
a month, it will have a different name: 11/1/2005. I am missing
something here.

Unless you want to write code to manipulate the query's properties each
time you run it, you'll be better off designing your query to calculate
the column-header fields as "Month1", "Month2", "Month3", etc. You
should be able to do this easily enough in the base query that the
crosstab query is transforming. Unfortunately, that means you have to
give up having the datasheet captions show the actual dates.

There are a couple of more complex alternatives: (1) use code modify
the query's properties each time you run it, or (b) when you display the
results on a form or report, use code in the object's Open event to
modify the captions of the labels that will serve as column headers.

 

[KARL DEWEY]

Use another query to pull a date in the first month of your data. Use the
same criteria in it as you use in the crosstab. The criteria could be hard as
here, from a prompt, or calculated.
SELECT Min([Change Requests].[Date open]) AS FirstMonth
FROM [Change Requests]
HAVING (((Min([Change Requests].[Date open])) Between #6/1/2001# And
#6/30/2002#));

Modify your crosstab by adding a column that is not displayed and edit
column content for label.
TRANSFORM Count([Change Requests].y) AS CountOfy
SELECT [Change Requests].x
FROM [Change Requests], Query71
WHERE ((([Change Requests].[Date open]) Between #6/1/2001# And #6/30/2002#))
GROUP BY [Change Requests].x, Format([Date open],"yyyy") & " " &
Format([Date open],"mm")
PIVOT "Month " & Month([Date open])-Month([Firstmonth])+1;

The group by Format([Date open],"yyyy") & " " & Format([Date open],"mm")
puts the columns in the correct display order.

The pivot "Month " & Month([Date open])-Month([Firstmonth])+1 substract the
month of the first date from the data month and then adds one to it. October
(10) minus October (10) plus one equals one for the first month display.
November (11) minus October (10) plus one equals two for the second month
display.

 

[Brian]

Pardon my slowness in catching on here, but what happens when my 12 months
run from November through October? Won't this:

Month([Date open])-Month([Firstmonth])+1

give me negative numbers for January (1) through October (10), since their
Months are smaller than the Month of November (11), the first month?

Use another query to pull a date in the first month of your data. Use the
same criteria in it as you use in the crosstab. The criteria could be hard as
here, from a prompt, or calculated.
SELECT Min([Change Requests].[Date open]) AS FirstMonth
FROM [Change Requests]
HAVING (((Min([Change Requests].[Date open])) Between #6/1/2001# And
#6/30/2002#));

Modify your crosstab by adding a column that is not displayed and edit
column content for label.
TRANSFORM Count([Change Requests].y) AS CountOfy
SELECT [Change Requests].x
FROM [Change Requests], Query71
WHERE ((([Change Requests].[Date open]) Between #6/1/2001# And #6/30/2002#))
GROUP BY [Change Requests].x, Format([Date open],"yyyy") & " " &
Format([Date open],"mm")
PIVOT "Month " & Month([Date open])-Month([Firstmonth])+1;

The group by Format([Date open],"yyyy") & " " & Format([Date open],"mm")
puts the columns in the correct display order.

The pivot "Month " & Month([Date open])-Month([Firstmonth])+1 substract the
month of the first date from the data month and then adds one to it. October
(10) minus October (10) plus one equals one for the first month display.
November (11) minus October (10) plus one equals two for the second month
display.

Clarification: I can see what the problem is, but I don't know how to get the
result I want. The ColumnHeadings property must use data from the data values
from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve months from
today, but how do I refer to each month generically so that I can refer to
them in the report.

Today, the first column is 10/1/2005, but when I run this report next a
month, it will have a different name: 11/1/2005. I am missing something here.

 

[Duane Hookom]

I prefer to use a column heading of:
ColHead: "Mth" & DateDiff("m", Date(), [DateField])
Set the column headings property to:
Column Headings: "Mth0", "Mth1", "Mth2",..."Mth11"

--
Duane Hookom
MS Access MVP
--

Pardon my slowness in catching on here, but what happens when my 12 months
run from November through October? Won't this:

Month([Date open])-Month([Firstmonth])+1

give me negative numbers for January (1) through October (10), since their
Months are smaller than the Month of November (11), the first month?

Use another query to pull a date in the first month of your data. Use
the
same criteria in it as you use in the crosstab. The criteria could be
hard as
here, from a prompt, or calculated.
SELECT Min([Change Requests].[Date open]) AS FirstMonth
FROM [Change Requests]
HAVING (((Min([Change Requests].[Date open])) Between #6/1/2001# And
#6/30/2002#));

Modify your crosstab by adding a column that is not displayed and edit
column content for label.
TRANSFORM Count([Change Requests].y) AS CountOfy
SELECT [Change Requests].x
FROM [Change Requests], Query71
WHERE ((([Change Requests].[Date open]) Between #6/1/2001# And
#6/30/2002#))
GROUP BY [Change Requests].x, Format([Date open],"yyyy") & " " &
Format([Date open],"mm")
PIVOT "Month " & Month([Date open])-Month([Firstmonth])+1;

The group by Format([Date open],"yyyy") & " " & Format([Date open],"mm")
puts the columns in the correct display order.

The pivot "Month " & Month([Date open])-Month([Firstmonth])+1 substract
the
month of the first date from the data month and then adds one to it.
October
(10) minus October (10) plus one equals one for the first month display.
November (11) minus October (10) plus one equals two for the second month
display.

Clarification: I can see what the problem is, but I don't know how to
get the
result I want. The ColumnHeadings property must use data from the data
values
from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve months
from
today, but how do I refer to each month generically so that I can refer
to
them in the report.

Today, the first column is 10/1/2005, but when I run this report next a
month, it will have a different name: 11/1/2005. I am missing something
here.

:

Here is the basic output of the crosstab query. The column headings
comes
from a DateSerial(Year(XXX),Month(XXX),1) to get totals by month:

ID 10/1/2005 11/1/2005 12/1/2005 -> etc. through 12 months
total
1115 359.216 78.901 12.789
1789 243.894 123.400 597.189
1934 12.774 1549.271 9.781

I tried setting column headings for my crosstab query as:
"ID", "Month1", "Month2" etc. -> "Month12"
but that gives me a "Data type mismatch in criteria expression".

I also tried just "Month1", "Month2" etc. -> "Month12" to see if it
was the
inclusion of the first column (my RowHeading column) that was causing
the
problem, but with the same error.

 

[Brian]

Bingo! I knew there had to be a way.

Thanks, Duane.

I prefer to use a column heading of:
ColHead: "Mth" & DateDiff("m", Date(), [DateField])
Set the column headings property to:
Column Headings: "Mth0", "Mth1", "Mth2",..."Mth11"

--
Duane Hookom
MS Access MVP
--

Pardon my slowness in catching on here, but what happens when my 12 months
run from November through October? Won't this:

Month([Date open])-Month([Firstmonth])+1

give me negative numbers for January (1) through October (10), since their
Months are smaller than the Month of November (11), the first month?

Use another query to pull a date in the first month of your data. Use
the
same criteria in it as you use in the crosstab. The criteria could be
hard as
here, from a prompt, or calculated.
SELECT Min([Change Requests].[Date open]) AS FirstMonth
FROM [Change Requests]
HAVING (((Min([Change Requests].[Date open])) Between #6/1/2001# And
#6/30/2002#));

Modify your crosstab by adding a column that is not displayed and edit
column content for label.
TRANSFORM Count([Change Requests].y) AS CountOfy
SELECT [Change Requests].x
FROM [Change Requests], Query71
WHERE ((([Change Requests].[Date open]) Between #6/1/2001# And
#6/30/2002#))
GROUP BY [Change Requests].x, Format([Date open],"yyyy") & " " &
Format([Date open],"mm")
PIVOT "Month " & Month([Date open])-Month([Firstmonth])+1;

The group by Format([Date open],"yyyy") & " " & Format([Date open],"mm")
puts the columns in the correct display order.

The pivot "Month " & Month([Date open])-Month([Firstmonth])+1 substract
the
month of the first date from the data month and then adds one to it.
October
(10) minus October (10) plus one equals one for the first month display.
November (11) minus October (10) plus one equals two for the second month
display.


:

Clarification: I can see what the problem is, but I don't know how to
get the
result I want. The ColumnHeadings property must use data from the data
values
from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve months
from
today, but how do I refer to each month generically so that I can refer
to
them in the report.

Today, the first column is 10/1/2005, but when I run this report next a
month, it will have a different name: 11/1/2005. I am missing something
here.

:

Here is the basic output of the crosstab query. The column headings
comes
from a DateSerial(Year(XXX),Month(XXX),1) to get totals by month:

ID 10/1/2005 11/1/2005 12/1/2005 -> etc. through 12 months
total
1115 359.216 78.901 12.789
1789 243.894 123.400 597.189
1934 12.774 1549.271 9.781

I tried setting column headings for my crosstab query as:
"ID", "Month1", "Month2" etc. -> "Month12"
but that gives me a "Data type mismatch in criteria expression".

I also tried just "Month1", "Month2" etc. -> "Month12" to see if it
was the
inclusion of the first column (my RowHeading column) that was causing
the
problem, but with the same error.

 

[KARL DEWEY]

You are right - I did not have a full year of dates in my data. Here is a
mix of Duane's and mine modified some more.

Bingo! I knew there had to be a way.

Thanks, Duane.

I prefer to use a column heading of:
ColHead: "Mth" & DateDiff("m", Date(), [DateField])
Set the column headings property to:
Column Headings: "Mth0", "Mth1", "Mth2",..."Mth11"

--
Duane Hookom
MS Access MVP
--

Pardon my slowness in catching on here, but what happens when my 12 months
run from November through October? Won't this:

Month([Date open])-Month([Firstmonth])+1

give me negative numbers for January (1) through October (10), since their
Months are smaller than the Month of November (11), the first month?

:

Use another query to pull a date in the first month of your data. Use
the
same criteria in it as you use in the crosstab. The criteria could be
hard as
here, from a prompt, or calculated.
SELECT Min([Change Requests].[Date open]) AS FirstMonth
FROM [Change Requests]
HAVING (((Min([Change Requests].[Date open])) Between #6/1/2001# And
#6/30/2002#));

Modify your crosstab by adding a column that is not displayed and edit
column content for label.
TRANSFORM Count([Change Requests].y) AS CountOfy
SELECT [Change Requests].x
FROM [Change Requests], Query71
WHERE ((([Change Requests].[Date open]) Between #6/1/2001# And
#6/30/2002#))
GROUP BY [Change Requests].x, Format([Date open],"yyyy") & " " &
Format([Date open],"mm")
PIVOT "Month " & Month([Date open])-Month([Firstmonth])+1;

The group by Format([Date open],"yyyy") & " " & Format([Date open],"mm")
puts the columns in the correct display order.

The pivot "Month " & Month([Date open])-Month([Firstmonth])+1 substract
the
month of the first date from the data month and then adds one to it.
October
(10) minus October (10) plus one equals one for the first month display.
November (11) minus October (10) plus one equals two for the second month
display.


:

Clarification: I can see what the problem is, but I don't know how to
get the
result I want. The ColumnHeadings property must use data from the data
values
from the Column Heading field.

Basically, I just need a sum/column for each of the next twelve months
from
today, but how do I refer to each month generically so that I can refer
to
them in the report.

Today, the first column is 10/1/2005, but when I run this report next a
month, it will have a different name: 11/1/2005. I am missing something
here.

:

Here is the basic output of the crosstab query. The column headings
comes
from a DateSerial(Year(XXX),Month(XXX),1) to get totals by month:

ID 10/1/2005 11/1/2005 12/1/2005 -> etc. through 12 months
total
1115 359.216 78.901 12.789
1789 243.894 123.400 597.189
1934 12.774 1549.271 9.781

I tried setting column headings for my crosstab query as:
"ID", "Month1", "Month2" etc. -> "Month12"
but that gives me a "Data type mismatch in criteria expression".

I also tried just "Month1", "Month2" etc. -> "Month12" to see if it
was the
inclusion of the first column (my RowHeading column) that was causing
the
problem, but with the same error.