Get 0 When There is No Value

[Paula]

I have the following crosstab query. Can any one tell me how to get 0 for
Daval when their is no value for that Value field.

PARAMETERS [Enter Number Of Summary Years] Short;
TRANSFORM
First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID
])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.Scree nCoID
WHERE
(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[Scre
enDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years]
And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e
ORDER BY
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) &
" " & [FldName] DESC
PIVOT
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) &
" " & [FldName];

Thanks!

Paula

[Salad]

Paula wrote:

I have the following crosstab query. Can any one tell me how to get 0 for
Daval when their is no value for that Value field.

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))

[Duane Hookom]

Try:
Val(Nz(First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],
[CountOfPatientID])),0) ) AS DaVal

Are you using a Tbl5YrSummaryColumns with just two records? Are you getting
this to work the way you need?

--
Duane Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:3f*****************@newsread2.news.atl.earthl ink.net...

I have the following crosstab query. Can any one tell me how to get 0 for
Daval when their is no value for that Value field.

PARAMETERS [Enter Number Of Summary Years] Short;
TRANSFORM
First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID ])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER JOIN Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.Scree nCoID
WHERE
(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[Scre enDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) & " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years]
And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e
ORDER BY
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) & " " & [FldName] DESC
PIVOT
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) & " " & [FldName];

Thanks!

Paula

[Paula]

Thank you for responding!

I tried that abd also surrounding the whole First statement before I posted
and still got no zeroes.

Paula

BTW, I prefer French over Oil and Vinegar!
"Salad" <oi*@vinegar.com> wrote in message
news:Ai*****************@newsread3.news.pas.earthl ink.net...

Paula wrote:

I have the following crosstab query. Can any one tell me how to get 0 for Daval when their is no value for that Value field.

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))

[Paula]

Thanks, Duane, that's it!!!

Yes, the crosstab is working fine! I get the number of years(columns) that I
enter in the parameter and I get Total Screenings and Total Screened (two
fields in Tbl5YrSummaryColumns) for each year. (Did you post this technique
not too long ago?)

Some things that make me uneasy though:
1. I had to use two separate totals queries to get CountOfScreenLogisticID
and CountOfPatientID then join these queries in the crosstab and use the
First function to make it all work. I tried just including the tables needed
to get ScreenLogisticID and PatientID then using Count in the crosstab but
that did not give the correct results.
2. As you might have noticed, I had to use Year(Date())+1 to get the Year
of the current date
3. No matter what order I used for the columns in the crosstab or what
order I put CountOfScreenLogisticID and CountOfPatientID in the expression,
Total Screened (CountOfPatientID) always came first in the crosstab results.

Any ideas on the above?

I really appreciate your help getting the zeroes!

Paula

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:#7**************@TK2MSFTNGP14.phx.gbl...

Try:
Val(Nz(First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],
[CountOfPatientID])),0) ) AS DaVal

Are you using a Tbl5YrSummaryColumns with just two records? Are you getting this to work the way you need?

--
Duane Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:3f*****************@newsread2.news.atl.earthl ink.net...

I have the following crosstab query. Can any one tell me how to get 0 for Daval when their is no value for that Value field.

PARAMETERS [Enter Number Of Summary Years] Short;
TRANSFORM

First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID

])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER

JOIN

Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.Scree nCoID
WHERE

(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[Scre

enDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])

& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years] And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e
ORDER BY

IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD

ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) &

" " & [FldName] DESC
PIVOT

IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD

ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear]) &

" " & [FldName];

Thanks!

Paula

[Paula]

See Duane Hookum's response!

Paula
"Salad" <oi*@vinegar.com> wrote in message
news:Ai*****************@newsread3.news.pas.earthl ink.net...

Paula wrote:

I have the following crosstab query. Can any one tell me how to get 0 for Daval when their is no value for that Value field.

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))

[Duane Hookom]

I might have been the OP of the cartesian crosstab with the table with two
records.
1) using First() in a crosstab often works very well
3) Total Screened comes before Total Screenings in alpha order.

--
Duane Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:AI*****************@newsread2.news.atl.earthl ink.net...

Thanks, Duane, that's it!!!

Yes, the crosstab is working fine! I get the number of years(columns) that I enter in the parameter and I get Total Screenings and Total Screened (two
fields in Tbl5YrSummaryColumns) for each year. (Did you post this technique not too long ago?)

Some things that make me uneasy though:
1. I had to use two separate totals queries to get CountOfScreenLogisticID and CountOfPatientID then join these queries in the crosstab and use the
First function to make it all work. I tried just including the tables needed to get ScreenLogisticID and PatientID then using Count in the crosstab but
that did not give the correct results.
2. As you might have noticed, I had to use Year(Date())+1 to get the Year
of the current date
3. No matter what order I used for the columns in the crosstab or what
order I put CountOfScreenLogisticID and CountOfPatientID in the expression, Total Screened (CountOfPatientID) always came first in the crosstab results.
Any ideas on the above?

I really appreciate your help getting the zeroes!

Paula

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:#7**************@TK2MSFTNGP14.phx.gbl...

Try:
Val(Nz(First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],
[CountOfPatientID])),0) ) AS DaVal

Are you using a Tbl5YrSummaryColumns with just two records? Are you

getting

this to work the way you need?

--
Duane Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:3f*****************@newsread2.news.atl.earthl ink.net...

I have the following crosstab query. Can any one tell me how to get 0 for Daval when their is no value for that Value field.

PARAMETERS [Enter Number Of Summary Years] Short;
TRANSFORM

First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID

])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e AS [Screening Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER

JOIN

Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.Scree nCoID
WHERE

(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[Scre

enDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])

& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years] And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoNam e
ORDER BY

IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD

ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])

&

" " & [FldName] DESC
PIVOT

IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD

ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])

&

" " & [FldName];

Thanks!

Paula