Month and Year criteria

[vickster3659]

Hello,
My crosstab query is using: Expr1: Format([DateDue],"mmm"). I would like to show the year as well. How would I do this, please?


Thank you

[June7]

Try:

Format([DateDue], "YYYYmmm")

[vickster3659]

Hi June7,
This did not work for me. I am using Access 2002, however, not sure if it would be different.
Thanks,

[June7]

What does 'did not work' mean, what happens - error message, wrong results, nothing?

Post the query SQL statement for analysis.

Shouldn't make any difference.

[vickster3659]

Sorry.....no data will show.

Here is the SQL:

TRANSFORM Count(qryCountPaymentsByBank.Amount) AS CountOfAmount
SELECT qryCountPaymentsByBank.BankID, qryCountPaymentsByBank.TransactionCode, Sum(qryCountPaymentsByBank.Amount) AS [Total Of Amount]
FROM qryCountPaymentsByBank
GROUP BY qryCountPaymentsByBank.BankID, qryCountPaymentsByBank.TransactionCode
ORDER BY qryCountPaymentsByBank.BankID, qryCountPaymentsByBank.TransactionCode
PIVOT Format([DateDue],"YYYYmmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

My Crosstab query is based on a Select query, with the date formatted as mm/dd/yyyy

Thank you

[June7]

I expect the issue is with the In clause. Try without it. How many years are in the database? The result should be field for each yyyymmm combination. Are you sure you want that?

[vickster3659]

Hi June7,
I took out the In clause, this gave me the expected results. I have 2 years, so far. I see why you asked if I really want this, since as time passes, this will become a rather large report very quickly, not conducive to printing. Therefore, I was able to make YYYY as a row header, which will make the report easier to print.
Thanks for your help!