My crosstab is built on one feild from a table and the corresponding feild
list table as a left-outer join. The crosstab returns the total by month for
each of the items in the field and if there were no records returns a zero
for a grand total. When I try to add the date field from the first table and
query for a specific time period, I lose all of the items with null values.
I have tried to use the NZ function, but with no luck. Either I am building
the query incorrectly of I have built my crosstab incorrectly. Would someone
please advise.

Presumably the date field is in the table that's on the outer side of the
join. Then the criteria eliminates the nulls, and so the net result is the
same as if you had used an inner join.

If that's what's going on, it might be simplest to split this into 2
queries. Create an ordinary SELECT query that returns the records you want
from the table on the outer side of the join, with the date criteria. Save
this query.

Now create a crosstab that uses this query as an input "table", instead of
the table you currently have. In this new query, use the outer join between
your table and this lower-level query. Since the criteria already operated
before the lower query returned its records, the outer join will work
correctly.

If you need to use parameters for the lower-level query, be sure to declare
them. For example, if you are using a form that has text boxes for StartDate
and EndDate, the criteria might be:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
Then in query design (in this lower-level query), choose Parameters on the
Query menu, and enter 2 rows:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

For other crosstab tricks:
http://allenbrowne.com/ser-67.html

--
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.

"Leah" <(E-Mail Removed)> wrote in message
news:E82E8A65-8EA9-431A-AADA-(E-Mail Removed)...
> My crosstab is built on one feild from a table and the corresponding feild
> list table as a left-outer join. The crosstab returns the total by month
> for
> each of the items in the field and if there were no records returns a zero
> for a grand total. When I try to add the date field from the first table
> and
> query for a specific time period, I lose all of the items with null
> values.
> I have tried to use the NZ function, but with no luck. Either I am
> building
> the query incorrectly of I have built my crosstab incorrectly. Would
> someone
> please advise.

Thank you, I have spent hours trying to figure this out.
Leah

"Allen Browne" wrote:

> Presumably the date field is in the table that's on the outer side of the
> join. Then the criteria eliminates the nulls, and so the net result is the
> same as if you had used an inner join.
>
> If that's what's going on, it might be simplest to split this into 2
> queries. Create an ordinary SELECT query that returns the records you want
> from the table on the outer side of the join, with the date criteria. Save
> this query.
>
> Now create a crosstab that uses this query as an input "table", instead of
> the table you currently have. In this new query, use the outer join between
> your table and this lower-level query. Since the criteria already operated
> before the lower query returned its records, the outer join will work
> correctly.
>
> If you need to use parameters for the lower-level query, be sure to declare
> them. For example, if you are using a form that has text boxes for StartDate
> and EndDate, the criteria might be:
> Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
> Then in query design (in this lower-level query), choose Parameters on the
> Query menu, and enter 2 rows:
> [Forms].[Form1].[StartDate] Date/Time
> [Forms].[Form1].[EndDate] Date/Time
>
> For other crosstab tricks:
> http://allenbrowne.com/ser-67.html
>
> --
> 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.
>
> "Leah" <(E-Mail Removed)> wrote in message
> news:E82E8A65-8EA9-431A-AADA-(E-Mail Removed)...
> > My crosstab is built on one feild from a table and the corresponding feild
> > list table as a left-outer join. The crosstab returns the total by month
> > for
> > each of the items in the field and if there were no records returns a zero
> > for a grand total. When I try to add the date field from the first table
> > and
> > query for a specific time period, I lose all of the items with null
> > values.
> > I have tried to use the NZ function, but with no luck. Either I am
> > building
> > the query incorrectly of I have built my crosstab incorrectly. Would
> > someone
> > please advise.
>
>