Show all months on report

[Supe]

I have a report that shows total shipments by month for specific items by
totaling in the Month Footer. Some items have no shipment on some months so
that month is not listed. Is there a way to have the report show all months
regardless if there were shipments that month or not?

 

[KARL DEWEY]

Create a table name CountNumber with field CountNUM having integers from 0
(zero) through you maximum spread.
Build the query below.
SELECT DateAdd("m",[CountNUM],CVDate([Enter start date])) AS [My Dates]
FROM CountNumber
WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])))<=CVDate([Enter
end date])));

Left join the above query in your select query.

 

[Evi]

I use a table, TblMonth containing only the numbers 1 to 12. It is Left
Joined to a field in a query based on the date field in my real table
containing:

Mnth:Month(MyDateField).



I then have to use Val and NZ on any calculations where I need data eg
Amounts

(using NZ([Amounts],0) on a null field seems to create a text field so I put
val around that to change it back into a number)
Val(NZ([Amounts],0))

If I need a value in the Mnth field (eg for crosstabs) I use
Val(NZ([Mnth],1)) so that those items which have never been used end up in
Month1

Evi