REPORT; If Null return "0"

I have created a database to track employee time. Within that, I have
also created a report off the following query:

SELECT ALLTasksFilter.ProjectName,
Sum([qryDateFilterTaskHours-Daily].NumberOfCompletions) AS
SumOfNumberOfCompletions,
Sum([qryDateFilterTaskHours-Daily].HoursWorked) AS SumOfHoursWorked,
[qryDateFilterTaskHours-Daily].WorkedByWho
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Daily] ON
ALLTasksFilter.ProjectName = [qryDateFilterTaskHours-Daily].ProjectName
GROUP BY ALLTasksFilter.ProjectName,
[qryDateFilterTaskHours-Daily].WorkedByWho;

The Report displays the Task Name, how many completions, and how many
hours.

The report shows ALL tasks, even those that do not have any completions
or hours attributed. Right now, the report shows a blank if there is
no reported completions or time. Is there any way to make it show a
"0" if not time has been documented?

The Pseudocode of:

If [Number of Completions] is null,
Then Me.Number of Completions = "0"

Else

If [HoursWorked] is null,
Then Me.Number of Completions = "0"

Else

Any suggestions would be greatly appreciated!

re: REPORT; If Null return "0"

"Drum2001" <drum2001@gmail.com> wrote in message
news:1145295158.172104.43020@z34g2000cwc.googlegro ups.com...[color=blue]
> I have created a database to track employee time. Within that, I have
> also created a report off the following query:
>
> SELECT ALLTasksFilter.ProjectName,
> Sum([qryDateFilterTaskHours-Daily].NumberOfCompletions) AS
> SumOfNumberOfCompletions,
> Sum([qryDateFilterTaskHours-Daily].HoursWorked) AS SumOfHoursWorked,
> [qryDateFilterTaskHours-Daily].WorkedByWho
> FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Daily] ON
> ALLTasksFilter.ProjectName = [qryDateFilterTaskHours-Daily].ProjectName
> GROUP BY ALLTasksFilter.ProjectName,
> [qryDateFilterTaskHours-Daily].WorkedByWho;
>
> The Report displays the Task Name, how many completions, and how many
> hours.
>
> The report shows ALL tasks, even those that do not have any completions
> or hours attributed. Right now, the report shows a blank if there is
> no reported completions or time. Is there any way to make it show a
> "0" if not time has been documented?
>
> The Pseudocode of:
>
> If [Number of Completions] is null,
> Then Me.Number of Completions = "0"
>
> Else
>
> If [HoursWorked] is null,
> Then Me.Number of Completions = "0"
>
> Else
>
> Any suggestions would be greatly appreciated!
>[/color]

If [Number of Completions] is null, Then

Me.Number of Completions = NZ([Number of Completions],0)

Else

Me.Number of Completions = NZ([HoursWorked],0)

End If

re: REPORT; If Null return "0"

Ron,

Where would I put this? The Report? The Query?

re: REPORT; If Null return "0"

"Drum2001" <drum2001@gmail.com> wrote in message
news:1145297615.879198.64760@e56g2000cwe.googlegro ups.com...[color=blue]
> Ron,
>
> Where would I put this? The Report? The Query?
>[/color]

1.In On Format event of the report

If isnull(me![Number of Completions])l, Then

Me![Number of Completions] = NZ(Me![Number of Completions],0)

Else

Me![Number of Completions] = NZ(me![HoursWorked],0)

End If

2. In the Control source property of the field in the report

= NZ([Number of Completions],NZ([HoursWorked],0))

3. As a calculated field in the query

NZ([Number of Completions],NZ([HoursWorked],0)) As NumberofCompletions

re: REPORT; If Null return "0"

None of those worked for me.

#1 Kept Errored Out
#2 Displayed all the resultes as ERROR#
#3 Getting "Wrong Syntax" error

Any ideas why?

paii, Ron wrote:[color=blue]
> "Drum2001" <drum2001@gmail.com> wrote in message
> news:1145297615.879198.64760@e56g2000cwe.googlegro ups.com...[color=green]
> > Ron,
> >
> > Where would I put this? The Report? The Query?
> >[/color]
>
> 1.In On Format event of the report
>
> If isnull(me![Number of Completions])l, Then
>
> Me![Number of Completions] = NZ(Me![Number of Completions],0)
>
> Else
>
> Me![Number of Completions] = NZ(me![HoursWorked],0)
>
> End If
>
> 2. In the Control source property of the field in the report
>
> = NZ([Number of Completions],NZ([HoursWorked],0))
>
> 3. As a calculated field in the query
>
> NZ([Number of Completions],NZ([HoursWorked],0)) As NumberofCompletions[/color]

re: REPORT; If Null return "0"

"Drum2001" <drum2001@gmail.com> wrote in message
news:1145365877.527955.302390@g10g2000cwb.googlegr oups.com...[color=blue]
> None of those worked for me.
>
> #1 Kept Errored Out
> #2 Displayed all the resultes as ERROR#
> #3 Getting "Wrong Syntax" error
>
> Any ideas why?
>[/color]

Can you give me examples of the code you entered?

re: REPORT; If Null return "0"

Change your query as follows:
SELECT ALLTasksFilter.ProjectName,
Sum(Nz([qryDateFilterTaskHours-Daily].NumberOfCompletions,0)) AS
SumOfNumberOfCompletions,
Sum(Nz([qryDateFilterTaskHours-Daily].HoursWorked,0)) AS
SumOfHoursWorked,
[qryDateFilterTaskHours-Daily].WorkedByWho
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTaskHours-Daily] ON
ALLTasksFilter.ProjectName = [qryDateFilterTaskHours-Daily].ProjectName

GROUP BY ALLTasksFilter.ProjectName,
[qryDateFilterTaskHours-Daily].WorkedByWho;

The Nz function converts null values to a specified value. For
instance, Nz(YourFieldName,0) would report any null values as zero. By
putting this inside your sum function, you are converting the nulls to
zero BEFORE it tries to sum them up. BTW, this works for all data
types, so long as your specified value matches the data type of
YourFieldName. If it is a string field that is null,
Nz(YourFieldName,"Blank") would spit out the word Blank.

HTH,
Jana

re: REPORT; If Null return "0"

GOT IT!

Thank you to all those who have helped me!