can't deem a textfield or calculated field as VALUE in Crosstab Query?

[sisieko]

TRANSFORM Max(" " & (Format([Start_Time] & [StartAM],"Medium Time") & " - " & Format([End_Time] & [EndAM],"Medium Time") & " " & [Program])) AS StartEndProgram
SELECT RequestTable.RoomReserved
FROM RequestTable
GROUP BY RequestTable.RoomReserved, RequestTable.StartDate, RequestTable.EndDate
PIVOT RequestTable.DateReserved In ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday");

I get the "error "datatype mismatch in criteria expression.

This what im trying to achieve:

i have database that allows a user to reserve a room for certain days throughout the week.
The table RoomRequest contains the following data:
DateReserved
RoomReserved
TimeStart
TimeEnd
Program

The form for entering data works correctly, I cannot seem to figure out how to create a report to display this information as intended.

The way i want it, is to have a column where RoomReserved is column1, then mon,tue,wed, .. in each column. i.e. The Reserved time for the day is listed for each Room. And i'll like to sort weekly. A weekly report.

This is how it should look

RoomReserved Monda y Tuesday Wednesday
Classroom1 8am-6pm 11am -2pm 9am - 2pm
Classroom2 4pm - 10 3pm -12noon 8am - 5pm
and so on
My question is, How can this layout be achieved in MsAccess considering my data table structure?

 

[sisieko]

*bump
please say something

 

[PHV]

Have you tried this ?
PIVOT Format(RequestTable.DateReserved, "dddd")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244