Crosstab Columns Not Showing in query results

T

tdietrich71

I have the following crosstab that gives me the desired results:

TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT In (8/25/2008,8/26/2008,8/27/2008,
8/28/2008,8/29/2008);


However, I want to get the dates in the PIVOT line from a form field, but
when I try to do that I get "Data type mismatch in query expression" using
the code below:

TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT In ([Forms]![frm_Schedule]!
[StartDate],[Forms]![frm_Schedule]![StartDate]+1,[Forms]![frm_Schedule]!
[StartDate]+2,[Forms]![frm_Schedule]![StartDate]+3,[Forms]![frm_Schedule]!
[StartDate]+4);

Both the form field StartDate AND the column MTG_DT in the table it comes
from are set to Date/Time (Short Date), but I still get the error.


I also tried:

PARAMETERS [Forms]![frm_Schedule]![StartDate] DateTime, [Forms]![frm_Schedule]
![EndDate] DateTime;
TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT;

Only get 8/25/2008, 8/26/2008, 8/27/2008 for MTG_DT. These are the only
dates that have values. 8/28/2008 and 8/29/2008 have all null values, but I
want those columns to appear in my result set anyway.....how do I fix this??
 
J

John Spencer

I don't know that this will work, but I would try the following.

PARAMETERS [Forms]![frm_Schedule]![StartDate] DateTime
, [Forms]![frm_Schedule]![EndDate] DateTime;
TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT In (
Cstr([Forms]![frm_Schedule]![StartDate])
,Cstr([Forms]![frm_Schedule]![StartDate]+1)
,Cstr([Forms]![frm_Schedule]![StartDate]+2)
,Cstr([Forms]![frm_Schedule]![StartDate]+3),
,Cstr([Forms]![frm_Schedule]![StartDate]+4))

The idea being that CStr will force a string value as the column titles.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have the following crosstab that gives me the desired results:

TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT In (8/25/2008,8/26/2008,8/27/2008,
8/28/2008,8/29/2008);


However, I want to get the dates in the PIVOT line from a form field, but
when I try to do that I get "Data type mismatch in query expression" using
the code below:

TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT In ([Forms]![frm_Schedule]!
[StartDate],[Forms]![frm_Schedule]![StartDate]+1,[Forms]![frm_Schedule]!
[StartDate]+2,[Forms]![frm_Schedule]![StartDate]+3,[Forms]![frm_Schedule]!
[StartDate]+4);

Both the form field StartDate AND the column MTG_DT in the table it comes
from are set to Date/Time (Short Date), but I still get the error.


I also tried:

PARAMETERS [Forms]![frm_Schedule]![StartDate] DateTime, [Forms]![frm_Schedule]
![EndDate] DateTime;
TRANSFORM Max(qry_Reports_Meeting_Schedule_1.Time) AS MaxOfTime
SELECT tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime AS Times
FROM tbl_ScheduleTimes LEFT JOIN qry_Reports_Meeting_Schedule_1 ON
tbl_ScheduleTimes.Times = qry_Reports_Meeting_Schedule_1.MtgTime
GROUP BY tbl_ScheduleTimes.ID, tbl_ScheduleTimes.ShortTime
ORDER BY tbl_ScheduleTimes.ID
PIVOT qry_Reports_Meeting_Schedule_1.MTG_DT;

Only get 8/25/2008, 8/26/2008, 8/27/2008 for MTG_DT. These are the only
dates that have values. 8/28/2008 and 8/29/2008 have all null values, but I
want those columns to appear in my result set anyway.....how do I fix this??
Click to expand...
 
T

tdietrich71 via AccessMonster.com

Could I possibly e-mail you a sample Db? I am going in circles with this and
it's driving me nuts.

Duane said:
If the results are going to be displayed in a report or form, you might try
the solution found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would
need to subtract days rather than months.
I have the following crosstab that gives me the desired results:
Click to expand...
[quoted text clipped - 40 lines]
dates that have values. 8/28/2008 and 8/29/2008 have all null values, but I
want those columns to appear in my result set anyway.....how do I fix this??
Click to expand...
Click to expand...
 
D

Duane Hookom

I would prefer that we help you here in the news group.

It looks like you are creating a report so the solution from my link should
work. If not, tell us what you are attempting to accomplish (your
requirements/specs).

--
Duane Hookom
Microsoft Access MVP


tdietrich71 via AccessMonster.com said:
Could I possibly e-mail you a sample Db? I am going in circles with this and
it's driving me nuts.

Duane said:
If the results are going to be displayed in a report or form, you might try
the solution found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would
need to subtract days rather than months.
I have the following crosstab that gives me the desired results:
Click to expand...
[quoted text clipped - 40 lines]
dates that have values. 8/28/2008 and 8/29/2008 have all null values, but I
want those columns to appear in my result set anyway.....how do I fix this??
Click to expand...
Click to expand...
Click to expand...
 
T

tdietrich71 via AccessMonster.com

I figured it out using the link you provided...just took me a little bit to
tweak it the way I want it. Thanks so much!!!



Duane said:
I would prefer that we help you here in the news group.

It looks like you are creating a report so the solution from my link should
work. If not, tell us what you are attempting to accomplish (your
requirements/specs).
Could I possibly e-mail you a sample Db? I am going in circles with this and
it's driving me nuts.
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Critera in Crosstab Query 2
How to display zero value in blank fields of Cross Tab query 0
Include empty values in crosstab query 5
Relative Date & Crosstab problem 2
Duplicate values in Crosstab 3
Combine two columns into one in a crosstab query 1
Crosstab Queries 2
Time questions with partition 9

Top