Null to reflect as zero

A

ant1983

Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
 
A

ant1983

Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;
 
K

KenSheridan via AccessMonster.com

Wrap the reference to any column in which you want to return a Null as a zero
in the Nz function e.g.

Nz(qryStatsBookingsBreakdown_Crosstab.FNB,0) AS FNB

Ken Sheridan
Stafford, England
Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;
Hi,
Click to expand...
[quoted text clipped - 9 lines]
Ta
Click to expand...
Click to expand...
 
D

Daryl S

If you don't want to touch the original query, you can create a new query
based on the original one. Add all the fields from the first query to the
Fields line in the second query. Then adjust any that might be blank that
should be zero as follows:

Change this:
qryStatsBookingsBreakdown_Crosstab.FNB
to this:
nz(qryStatsBookingsBreakdown_Crosstab.FNB,0)

--
Daryl S


ant1983 said:
Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;


ant1983 said:
Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
Click to expand...
Click to expand...
 
A

ant1983

Hey Daryl,

Ta Mate that worked great though not so well for one of the fields:

"Total Of CountOfnumTrainingSessionID" - What do i do with this little guy?


Daryl S said:
If you don't want to touch the original query, you can create a new query
based on the original one. Add all the fields from the first query to the
Fields line in the second query. Then adjust any that might be blank that
should be zero as follows:

Change this:
qryStatsBookingsBreakdown_Crosstab.FNB
to this:
nz(qryStatsBookingsBreakdown_Crosstab.FNB,0)

--
Daryl S


ant1983 said:
Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;


ant1983 said:
Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
Click to expand...
Click to expand...
Click to expand...
 
A

ant1983

Typo - it worked fine for that one...

LAst one? LOL:

One of the fields is a percentage. if i apply that to the field it loses
the % sign and changes from (eg) 55% to 0.55

ant1983 said:
Hey Daryl,

Ta Mate that worked great though not so well for one of the fields:

"Total Of CountOfnumTrainingSessionID" - What do i do with this little guy?


Daryl S said:
If you don't want to touch the original query, you can create a new query
based on the original one. Add all the fields from the first query to the
Fields line in the second query. Then adjust any that might be blank that
should be zero as follows:

Change this:
qryStatsBookingsBreakdown_Crosstab.FNB
to this:
nz(qryStatsBookingsBreakdown_Crosstab.FNB,0)

--
Daryl S


ant1983 said:
Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;


:

Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
Click to expand...
Click to expand...
Click to expand...
 
A

ant1983

I got it working with Daryls help though still have a prob with the
percentage fields. Changes from 55% to 0.55?

KenSheridan via AccessMonster.com said:
Wrap the reference to any column in which you want to return a Null as a zero
in the Nz function e.g.

Nz(qryStatsBookingsBreakdown_Crosstab.FNB,0) AS FNB

Ken Sheridan
Stafford, England
Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;
Hi,
Click to expand...
[quoted text clipped - 9 lines]
Ta
Click to expand...
Click to expand...

--



.
Click to expand...
 
J

John Spencer

AND .55 is 55%. So all you need to do is apply the percentage format property
to the control that is using the value.

If you are actually seeing 55% in the field, then it may already be a string.
SO you could try
NZ([NameOfTheField],"0")
but I don't think that is going to work for you.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Typo - it worked fine for that one...

LAst one? LOL:

One of the fields is a percentage. if i apply that to the field it loses
the % sign and changes from (eg) 55% to 0.55

ant1983 said:
Hey Daryl,

Ta Mate that worked great though not so well for one of the fields:

"Total Of CountOfnumTrainingSessionID" - What do i do with this little guy?


Daryl S said:
If you don't want to touch the original query, you can create a new query
based on the original one. Add all the fields from the first query to the
Fields line in the second query. Then adjust any that might be blank that
should be zero as follows:

Change this:
qryStatsBookingsBreakdown_Crosstab.FNB
to this:
nz(qryStatsBookingsBreakdown_Crosstab.FNB,0)

--
Daryl S


:

Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;


:

Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

As John said, you can format that column in the second query. Keep the value
the same, but select the entire column for that field and right-mouse click
and choose Properties. You can format it to be percents that way.

--
Daryl S


ant1983 said:
Typo - it worked fine for that one...

LAst one? LOL:

One of the fields is a percentage. if i apply that to the field it loses
the % sign and changes from (eg) 55% to 0.55

ant1983 said:
Hey Daryl,

Ta Mate that worked great though not so well for one of the fields:

"Total Of CountOfnumTrainingSessionID" - What do i do with this little guy?


Daryl S said:
If you don't want to touch the original query, you can create a new query
based on the original one. Add all the fields from the first query to the
Fields line in the second query. Then adjust any that might be blank that
should be zero as follows:

Change this:
qryStatsBookingsBreakdown_Crosstab.FNB
to this:
nz(qryStatsBookingsBreakdown_Crosstab.FNB,0)

--
Daryl S


:

Whoops... Here we go:

SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate,
suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation,
tblClientContact.txtCompany, tblFacilitator.txtName, suptblCity.txtCity,
suptblSuburbs.txtSuburb, tblTrainingSession.txtVenue,
tblTrainingSession.numRoom, tblTrainingSession.numSeatsAvailable,
qryStatsBookingsBreakdown_Crosstab.FNB,
qryStatsBookingsBreakdown_Crosstab.FRCC,
qryStatsBookingsBreakdown_Crosstab.MOM,
qryStatsBookingsBreakdown_Crosstab.RMB,
qryStatsBookingsBreakdown_Crosstab.NON,
qryStatsBookingsBreakdown_Crosstab.[Total Of CountOfnumTrainingSessionID],
[Total Of CountOfnumTrainingSessionID]/[numSeatsAvailable] AS
PercentageBooked, qryStatsAttendanceBreakdown_Crosstab.FNB,
qryStatsAttendanceBreakdown_Crosstab.FRCC,
qryStatsAttendanceBreakdown_Crosstab.MOM,
qryStatsAttendanceBreakdown_Crosstab.RMB,
qryStatsAttendanceBreakdown_Crosstab.NON,
qryStatsAttendanceBreakdown_Crosstab.[Total Of CountOfnumAttended], [Total Of
CountOfnumAttended]/[Total Of CountOfnumTrainingSessionID] AS TurnoutRate,
[Total Of CountOfnumAttended]/[numSeatsAvailable] AS Capacity,
[numSeatsAvailable]-[Total Of CountOfnumTrainingSessionID] AS SeatsRemaining
FROM suptblSuburbs RIGHT JOIN (suptblCity RIGHT JOIN (tblFacilitator RIGHT
JOIN (tblClientContact RIGHT JOIN (suptblLevel RIGHT JOIN (suptblCourse RIGHT
JOIN (qryStatsAttendanceBreakdown_Crosstab RIGHT JOIN
(qryStatsBookingsBreakdown_Crosstab RIGHT JOIN tblTrainingSession ON
qryStatsBookingsBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON
qryStatsAttendanceBreakdown_Crosstab.numTrainingSessionID =
tblTrainingSession.autTrainingSessionID) ON suptblCourse.autCourseID =
tblTrainingSession.numCourse) ON suptblLevel.autLevelID =
tblTrainingSession.numLevel) ON tblClientContact.autClientID =
tblTrainingSession.numClient) ON tblFacilitator.autFacilitatorID =
tblTrainingSession.numFacilitator) ON suptblCity.autCityID =
tblTrainingSession.numCity) ON suptblSuburbs.autSuburbID =
tblTrainingSession.numSuburb
WHERE (((tblTrainingSession.blnCurrentTraining)=No))
ORDER BY tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate;


:

Hi,

I have a query with loads of tables and crosstabs. Its basically a stats
report.

My problem is that sometimes there are cells that are blank but i dont want
that. I'd prefer it to reflect as a zero. how do i do that?

Er, im no sql boffen but im guessing you are going to ask for that so here
it is. LOL Please dont reply in sql language as i wouldnt know what to do
with it :)

Ta
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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.

Ask a Question

Similar Threads

Null to Zero in a crosstab 1
change null count in crosstab qry to zeros Trouble with Nz 9
Display all results, even zeros, nulls, and blanks 3
Show zeros for null in crosstab query 1
How do I count the nulls in a field on a table not related to my f 11
Combine IIf with NZ to get null for zero calculated sum? 3
cannot sum null; not zero 4
If null then display zero 1

Top