calc field shows as null in query

[Pat]

My query is:
SELECT ImportINGTable.TypeofTrans, Format([Date],"yyyy/mm") AS yyyymm,
DSum("Credit","ImportINGTable","Format(Date,'yyyy/mm') =" & [yyyymm] & " AND No4Pen=1")*[PHP rate or 1 =$] AS Pension
FROM ImportINGTable
GROUP BY ImportINGTable.TypeofTrans, Format([Date],"yyyy/mm")
HAVING (((ImportINGTable.TypeofTrans) Is Null));

I am nonplussed, what have a I done wrong?

[HansV]

I think the problem is that yyyymm is a text value. Does this work for you?

SELECT Year([Date]) As Y, Month([Date]) AS M, DSum("Credit", "ImportINGTable", "Year([Date])=" & [Y] & " AND Month([Date])=" & [M] & " AND No4Pen=1")*[PHP rate or 1 =$] AS Pension
FROM ImportINGTable
WHERE ImportINGTable.TypeofTrans Is Null
GROUP BY Year([Date]), Month([Date])

[Pat]

I have solved it by introducing another field into the table which contains a yyyy/mm as a text field and changing the DSums accordingly.

[HansV]

OK. that may be more efficient too.

[Pat]

Thanks Hans, always good to see your solutions as there is generally more than one way to skin a cat, and you seem to know them all.