Prev: Referential Integrity Q
Next: church member database
From: tighe on 4 May 2010 15:36
i have a form based on a crosstab query that sums on threee possible types
A, B or C. on the form the field for B shows #Name?, if B does not exist.
this is ok but i need it to show 0, any suggestions?

i tried playng with the query, but no luck there by adding a Type table.
From: KARL DEWEY on 4 May 2010 16:18
You could use this --
My Stuff: IIf([B] Is Null,0,[B])

But it will prompt for [B] if it does not exist.

--
Build a little, test a little.


"tighe" wrote:

> i have a form based on a crosstab query that sums on threee possible types
> A, B or C. on the form the field for B shows #Name?, if B does not exist.
> this is ok but i need it to show 0, any suggestions?
>
> i tried playng with the query, but no luck there by adding a Type table.
From: tighe on 4 May 2010 18:02
Karl,
thanks for the response, the #name? was appearing becasue of the
non-existence of [B].

i ended up adding a parameter to the crosstab queury, more than likely [B]
will exist for the whole office.

"KARL DEWEY" wrote:

> You could use this --
> My Stuff: IIf([B] Is Null,0,[B])
>
> But it will prompt for [B] if it does not exist.
>
> --
> Build a little, test a little.
>
>
> "tighe" wrote:
>
> > i have a form based on a crosstab query that sums on threee possible types
> > A, B or C. on the form the field for B shows #Name?, if B does not exist.
> > this is ok but i need it to show 0, any suggestions?
> >
> > i tried playng with the query, but no luck there by adding a Type table.
From: KARL DEWEY on 4 May 2010 19:34
Did you consider modifying the PIVOT statement to include [B] at all times
like this --
PIVOT YourCurrentValue IN("A", "B", "C", "D", ... )

They will appear and in the exact order you put them in the parenthesis.

--
Build a little, test a little.


"tighe" wrote:

> Karl,
> thanks for the response, the #name? was appearing becasue of the
> non-existence of [B].
>
> i ended up adding a parameter to the crosstab queury, more than likely [B]
> will exist for the whole office.
>
> "KARL DEWEY" wrote:
>
> > You could use this --
> > My Stuff: IIf([B] Is Null,0,[B])
> >
> > But it will prompt for [B] if it does not exist.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "tighe" wrote:
> >
> > > i have a form based on a crosstab query that sums on threee possible types
> > > A, B or C. on the form the field for B shows #Name?, if B does not exist.
> > > this is ok but i need it to show 0, any suggestions?
> > >
> > > i tried playng with the query, but no luck there by adding a Type table.
 | 
Pages: 1
Prev: Referential Integrity Q
Next: church member database