By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

REPORT; If Null return "0"

P: n/a
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!

Apr 17 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com...
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!


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
Apr 17 '06 #2

P: n/a
Ron,

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

Apr 17 '06 #3

P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Ron,

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


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
Apr 17 '06 #4

P: n/a
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:
"Drum2001" <dr******@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Ron,

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


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


Apr 18 '06 #5

P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
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?


Can you give me examples of the code you entered?
Apr 18 '06 #6

P: n/a
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

Apr 19 '06 #7

P: n/a
GOT IT!

Thank you to all those who have helped me!

Apr 20 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.