fields not available in crosstab need to accomodate

C

cutthroatjess

Greetings,

Here is my sql statement from a select query that queries a crosstab query.

SELECT [test 10day_Crosstab].Dateofcoldate, [test 10day_Crosstab].PMSC_TDS,
CDate([dateofcoldate]) AS formatdate,
IIf(IsError([tds])=True,[pmsc_tds],IIf(IsNull([tds])=False,Round([tds],0),Round([pmsc_tds],0))) AS acceptedtds
FROM [test 10day_Crosstab]
ORDER BY CDate([dateofcoldate]);

The problem is, as I've tried to correct with the iserror function, is that
the field [tds] sometimes shows in the crosstab and other times not. When it
doesn't it gives an error. I need to somehow build in an exception for when
[tds] isn't there and just have it use what is.

Any Ideas?

Thanks!

Jesse
 
D

Duane Hookom

Can you enter all possible column names into the crosstab's column headings
property?
 
C

cutthroatjess

I can! It seems to work now, leaving [tds] null for non-values, and
populating them when they exist.
Thanks!

Jesse

Duane Hookom said:
Can you enter all possible column names into the crosstab's column headings
property?

--
Duane Hookom
MS Access MVP
--

cutthroatjess said:
Greetings,

Here is my sql statement from a select query that queries a crosstab
query.

SELECT [test 10day_Crosstab].Dateofcoldate, [test
10day_Crosstab].PMSC_TDS,
CDate([dateofcoldate]) AS formatdate,
IIf(IsError([tds])=True,[pmsc_tds],IIf(IsNull([tds])=False,Round([tds],0),Round([pmsc_tds],0)))
AS acceptedtds
FROM [test 10day_Crosstab]
ORDER BY CDate([dateofcoldate]);

The problem is, as I've tried to correct with the iserror function, is
that
the field [tds] sometimes shows in the crosstab and other times not. When
it
doesn't it gives an error. I need to somehow build in an exception for
when
[tds] isn't there and just have it use what is.

Any Ideas?

Thanks!

Jesse
Click to expand...
Click to expand...
 
Top