Parameters in a crosstab.

[grantschneider]

I have a crosstab query and I want to make sure it only queries data
from a single year. I have a column that is set up mmmYYYY (this
column in the table is a TEXT column). I want to use that column to
query only the data for a specific year. This is what I have tried and
it doesnt seem to work. Also, i would be interested in understanding
how to do this with a standard date column, format: dd/mm/yy.


PARAMETERS [Enter Year] Text ( 255 );
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (((MSF.MonthC) Like "*[Enter Year]"))
GROUP BY MSF.SVP
PIVOT MSF.Account;

Thanks,
Grant

 

[Allen Browne]

Try concatenating the wild card onto the front of the parameter:

PARAMETERS [Enter Year] Text ( 255 );
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (((MSF.MonthC) Like "*" & [Enter Year]))
GROUP BY MSF.SVP
PIVOT MSF.Account;

It would be more efficient to use a real Date/Time field.
Here's how (assuming the field is named MyDate):

PARAMETERS [Enter Year] Long;
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (MSF.[MyDate] >= DateSerial([Enter Year],1,1))
AND (MSF.[MyDate] < DateSerial([Enter Year]+1,1,1))
GROUP BY MSF.SVP
PIVOT MSF.Account;

 

[grantschneider]

Try concatenating the wild card onto the front of the parameter:

PARAMETERS [Enter Year] Text ( 255 );
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (((MSF.MonthC) Like "*" & [Enter Year]))
GROUP BY MSF.SVP
PIVOT MSF.Account;

It would be more efficient to use a real Date/Time field.
Here's how (assuming the field is named MyDate):

PARAMETERS [Enter Year] Long;
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (MSF.[MyDate] >= DateSerial([Enter Year],1,1))
AND (MSF.[MyDate] < DateSerial([Enter Year]+1,1,1))
GROUP BY MSF.SVP
PIVOT MSF.Account;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query and I want to make sure it only queries data
from a single year. I have a column that is set up mmmYYYY (this
column in the table is a TEXT column). I want to use that column to
query only the data for a specific year. This is what I have tried and
it doesnt seem to work. Also, i would be interested in understanding
how to do this with a standard date column, format: dd/mm/yy.

PARAMETERS [Enter Year] Text ( 255 );
TRANSFORM Sum(MSF.[Share Total]) AS [SumOfShare Total]
SELECT MSF.SVP, Sum(MSF.[Share Total]) AS [Total Of Share Total]
FROM MSF
WHERE (((MSF.MonthC) Like "*[Enter Year]"))
GROUP BY MSF.SVP
PIVOT MSF.Account;

Thanks,
Grant- Hide quoted text -

- Show quoted text -

Is there anywhere I can find a substantial introduction to date
functions all in one place?

 

[Allen Browne]

Open the code window in Access (e.g. press Ctrl+G).

Click help on the toolbar.
Search for:
Date Functions

In A2003, this returns about 40 results, covering most of what you need.