Get 0 When There is No Value

  • Thread starter Thread starter Paula
  • Start date Start date
P

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.ScreenCoName AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
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.ScreenCoName
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,
Have you tried...

First(IIf([FldName]="Screenings",NZ([CountOfScreenLogisticID]),NZ([CountOfPatientID])))
AS DaVal....

hth
Al Camp
 
Al,
Thanks for the quick response! Yes, tried that but no zeroes. Also tried the
NZ function around the whole First statement and no zeroes. Also tried
checking if the two counts = "" and that gave me a data type error.

Paula


AlCamp said:
Paula,
Have you tried...

First(IIf([FldName]="Screenings",NZ([CountOfScreenLogisticID]),NZ([CountOfPa
tientID])))
AS DaVal....

hth
Al Camp

Paula said:
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.ScreenCoName AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER
JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
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.ScreenCoName
ORDER BY
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName] DESC
PIVOT
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName];

Thanks!

Paula
Click to expand...
Click to expand...
 
Paula said:
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.
Click to expand...

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))
 
Paula,
Try a new post, and please include what you've already tried with your
question. You probably should use the NZ anyway.
hth
Al Camp


Pauls said:
Al,
Thanks for the quick response! Yes, tried that but no zeroes. Also tried
the
NZ function around the whole First statement and no zeroes. Also tried
checking if the two counts = "" and that gave me a data type error.

Paula


AlCamp said:
Paula,
Have you tried...

First(IIf([FldName]="Screenings",NZ([CountOfScreenLogisticID]),NZ([CountOfPa
tientID])))
AS DaVal....

hth
Al Camp

Paula said:
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.ScreenCoName AS
[Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER
JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
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.ScreenCoName
ORDER BY
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName] DESC
PIVOT
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName];

Thanks!

Paula
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.ScreenCoName AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
WHERE
(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years]
And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoName
ORDER BY
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
Click to expand...
&
" " & [FldName] DESC
PIVOT
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
Click to expand...
&
" " & [FldName];

Thanks!

Paula
Click to expand...
 
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 said:
Paula said:
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.
Click to expand...

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))
Click to expand...
 
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 said:
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 said:
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
Click to expand...
First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID
])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoName AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear INNER JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
WHERE
Click to expand...
(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years]
And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoName
ORDER BY
Click to expand...
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName] DESC
PIVOT
Click to expand...
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName];

Thanks!

Paula
Click to expand...
Click to expand...
 
See Duane Hookum's response!

Paula


Salad said:
Paula said:
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.
Click to expand...

I might change it to

First(IIf([FldName]="Screenings",
NZ([CountOfScreenLogisticID],0),NZ([CountOfPatientID],0)))
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
First(IIf([FldName]="Screenings",[CountOfScreenLogisticID],[CountOfPatientID
])) AS DaVal
SELECT Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoName AS [Screening
Partner]
FROM Tbl5YrSummaryColumns, Qry5YrSummaryNumberOfScreeningsPerYear
Click to expand...
INNER
JOIN
Qry5YrSummaryNumberOfPatientsScreenedPerYear ON
Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoID =
Qry5YrSummaryNumberOfPatientsScreenedPerYear.ScreenCoID
WHERE
Click to expand...
Click to expand...
(((IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenDateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
& " " & [FldName]) Between Year(Date())+1-[Enter Number Of Summary Years]
And Year(Date())+1))
GROUP BY Qry5YrSummaryNumberOfScreeningsPerYear.ScreenCoName
ORDER BY
Click to expand...
Click to expand...
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName] DESC
PIVOT
Click to expand...
Click to expand...
IIf([FldName]="Screenings",[Qry5YrSummaryNumberOfScreeningsPerYear].[ScreenD
ateYear],[Qry5YrSummaryNumberOfPatientsScreenedPerYear].[ScreenDateYear])
&
" " & [FldName];

Thanks!

Paula
Click to expand...
Click to expand...
Click to expand...
 
Back
Top