General discussion

Locked

MS Access Crosstab/Parameter Queries

By juddly ·
I'm writing a crosstab query but I want the user to input the date. In a simple SELECT query, you simply say, "WHERE Date = [ENTER DATE]." However, in an equivalent Crosstab Query, I get the error, "The Microsoft Jet Engine does not recognize [ENTER DATE] as a valid field name or expression."
What is different about pivot queries? Any work-arounds? I've tried putting the user-entry parameter in a sub-query with the same result.

TRANSFORM Sum(m.Monthly_Total) AS Total
SELECT m.division
FROM tbl_Mthly_Data AS m
WHERE m.as_of_date=[enter date:]
GROUP BY m.division
PIVOT m.as_of_date;

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Declare Parameters

by juddly In reply to MS Access Crosstab/Parame ...

Simple Answer found:

PARAMETERS [enter date:] DateTime;
TRANSFORM Sum(m.Monthly_Total) AS Total
SELECT m.division
FROM tbl_Mthly_Data AS m
WHERE m.as_of_date=[enter date:]
GROUP BY m.division
PIVOT m.as_of_date;

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums