In SQL view of the crosstab query in the PIVOT line add the months just
before the semicolon like this --
PIVOT Format([Date open],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
--
KARL DEWEY
Build a little - Test a little
[email protected] said:I run a query where the data pops up as #name? when there is no data,
is there away to convert this to show as 0 or blank when there is no
data? I run a crosstab query that populates data into months, someones
have no data and reflect #name?Ryan
[email protected] said:That's for titles right of the months? If I'm reading your reponse
correctly, I meant to say is that the data looks like this.
jan feb mar apr etc
1002 985 #name? 1001
There is no volume in mar so in the crosstab query data comes up as
#name?, i just want 0 or blank.
In SQL view of the crosstab query in the PIVOT line add the months just
before the semicolon like this --
PIVOT Format([Date open],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
--
KARL DEWEY
Build a little - Test a little
[email protected] said:I run a query where the data pops up as #name? when there is no data,
is there away to convert this to show as 0 or blank when there is no
data? I run a crosstab query that populates data into months, someones
have no data and reflect #name?Ryan
Thats odd! I was thinking the #name? was in the report. Post your SQL..
--
KARL DEWEY
Build a little - Test a little
[email protected] said:That's for titles right of the months? If I'm reading your reponse
correctly, I meant to say is that the data looks like this.jan feb mar apr etc
1002 985 #name? 1001There is no volume in mar so in the crosstab query data comes up as
#name?, i just want 0 or blank.In SQL view of the crosstab query in the PIVOT line add the months just
before the semicolon like this --
PIVOT Format([Date open],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
--
KARL DEWEY
Build a little - Test a little
:
I run a query where the data pops up as #name? when there is no data,
is there away to convert this to show as 0 or blank when there is no
data? I run a crosstab query that populates data into months, someones
have no data and reflect #name?
Ryan
[email protected] said:I also clicked on the crosstab and created a tabular form from it, so
in form view it shows the #name?, in the crosstab section it shows
just the months that have data. Here's SQL.
TRANSFORM Sum(QryAdageItemLookupsum.SumOfin_tran_qty) AS
SumOfSumOfin_tran_qty
SELECT QryAdageItemLookupsum.in_whs_key,
QryAdageItemLookupsum.in_item_key, QryAdageItemLookupsum.in_desc,
QryAdageItemLookupsum.uom_key,
Sum(QryAdageItemLookupsum.SumOfin_tran_qty) AS [Total Of
SumOfin_tran_qty]
FROM QryAdageItemLookupsum
GROUP BY QryAdageItemLookupsum.in_whs_key,
QryAdageItemLookupsum.in_item_key, QryAdageItemLookupsum.in_desc,
QryAdageItemLookupsum.uom_key
PIVOT QryAdageItemLookupsum.[Rec Month];
Thats odd! I was thinking the #name? was in the report. Post your SQL..
--
KARL DEWEY
Build a little - Test a little
[email protected] said:That's for titles right of the months? If I'm reading your reponse
correctly, I meant to say is that the data looks like this.jan feb mar apr etc
1002 985 #name? 1001There is no volume in mar so in the crosstab query data comes up as
#name?, i just want 0 or blank.On Sep 11, 2:36 pm, KARL DEWEY <[email protected]>
wrote:
In SQL view of the crosstab query in the PIVOT line add the months just
before the semicolon like this --
PIVOT Format([Date open],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");:
I run a query where the data pops up as #name? when there is no data,
is there away to convert this to show as 0 or blank when there is no
data? I run a crosstab query that populates data into months, someones
have no data and reflect #name?Ryan
Try this ---
TRANSFORM Sum(Nz(QryAdageItemLookupsum.SumOfin_tran_qty),0) AS
SumOfSumOfin_tran_qty
--
KARL DEWEY
Build a little - Test a little
[email protected] said:I also clicked on the crosstab and created a tabular form from it, so
in form view it shows the #name?, in the crosstab section it shows
just the months that have data. Here's SQL.TRANSFORM Sum(QryAdageItemLookupsum.SumOfin_tran_qty) AS
SumOfSumOfin_tran_qty
SELECT QryAdageItemLookupsum.in_whs_key,
QryAdageItemLookupsum.in_item_key, QryAdageItemLookupsum.in_desc,
QryAdageItemLookupsum.uom_key,
Sum(QryAdageItemLookupsum.SumOfin_tran_qty) AS [Total Of
SumOfin_tran_qty]
FROM QryAdageItemLookupsum
GROUP BY QryAdageItemLookupsum.in_whs_key,
QryAdageItemLookupsum.in_item_key, QryAdageItemLookupsum.in_desc,
QryAdageItemLookupsum.uom_key
PIVOT QryAdageItemLookupsum.[Rec Month];Thats odd! I was thinking the #name? was in the report. Post yourSQL..
--
KARL DEWEY
Build a little - Test a little
:
That's for titles right of the months? If I'm reading your reponse
correctly, I meant to say is that the data looks like this.
jan feb mar apr etc
1002 985 #name? 1001
There is no volume in mar so in the crosstab query data comes up as
#name?, i just want 0 or blank.
On Sep 11, 2:36 pm, KARL DEWEY <[email protected]>
wrote:
In SQL view of the crosstab query in the PIVOT line add the months just
before the semicolon like this --
PIVOT Format([Date open],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
--
KARL DEWEY
Build a little - Test a little
:
I run a query where the data pops up as #name? when there is nodata,
is there away to convert this to show as 0 or blank when there is no
data? I run a crosstab query that populates data into months, someones
have no data and reflect #name?
Ryan
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.