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([qryDateFilterTa skHours-Daily].NumberOfComple tions) AS
SumOfNumberOfCo mpletions,
Sum([qryDateFilterTa skHours-Daily].HoursWorked) AS SumOfHoursWorke d,
[qryDateFilterTa skHours-Daily].WorkedByWho
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Daily] ON
ALLTasksFilter. ProjectName = [qryDateFilterTa skHours-Daily].ProjectName
GROUP BY ALLTasksFilter. ProjectName,
[qryDateFilterTa skHours-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!
7  20693 
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** *************@z 34g2000cwc.goog legroups.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([qryDateFilterTa skHours-Daily].NumberOfComple tions) AS
SumOfNumberOfCo mpletions,
Sum([qryDateFilterTa skHours-Daily].HoursWorked) AS SumOfHoursWorke d,
[qryDateFilterTa skHours-Daily].WorkedByWho
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Daily] ON
ALLTasksFilter. ProjectName = [qryDateFilterTa skHours-Daily].ProjectName
GROUP BY ALLTasksFilter. ProjectName,
[qryDateFilterTa skHours-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
Ron,
Where would I put this? The Report? The Query?
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.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 NumberofComplet ions
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******** *************@e 56g2000cwe.goog legroups.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 NumberofComplet ions
"Drum2001" <dr******@gmail .com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.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?
Change your query as follows:
SELECT ALLTasksFilter. ProjectName,
Sum(Nz([qryDateFilterTa skHours-Daily].NumberOfComple tions,0)) AS
SumOfNumberOfCo mpletions,
Sum(Nz([qryDateFilterTa skHours-Daily].HoursWorked,0) ) AS
SumOfHoursWorke d,
[qryDateFilterTa skHours-Daily].WorkedByWho
FROM ALLTasksFilter LEFT JOIN [qryDateFilterTa skHours-Daily] ON
ALLTasksFilter. ProjectName = [qryDateFilterTa skHours-Daily].ProjectName
GROUP BY ALLTasksFilter. ProjectName,
[qryDateFilterTa skHours-Daily].WorkedByWho;
The Nz function converts null values to a specified value. For
instance, Nz(YourFieldNam e,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(YourFieldNam e,"Blank") would spit out the word Blank.
HTH,
Jana
GOT IT!
Thank you to all those who have helped me!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Pablo J Royo |
last post by:
Hello:
i have a function that reads a file as an argument and returns a reference
to an object that contains some information obtained from the file: FData
&ReadFile(string FilePath);
But , for example, when the file doesnt exists, i should not return any
reference to a bad constructed object, so i need something as a NULL
reference object. I suppose i could return a pointer instead, but i have
some code written with references which...
|
by: Don Vaillancourt |
last post by:
What's going on with Javascript.
At the beginning there was the "undefined" value which represented an
object which really didn't exist then came the null keyword. But
yesterday I stumbled across "null" string.
I know that I will get an "undefined" when I try to retrieve something
from the DOM which doesn't exist.
I have used null myself to initialize or reset variables. But in which
|
by: ~TheIcemanCometh~ |
last post by:
I have a textbox field on a report that shows an Address. The field is
built up in code when the user selects the customer in the following
manner:
ShipAddress = CustomerName & vbCR & CustomerAddress & vbCR & Customer
City (etc.)
This is done and put into the ShipAddress field on another table.
|
by: Anastasios Papadopoulos |
last post by:
Hello all,
I have statements like the following in my Property Get:
Public Property AccountNumber() As String
Get
Return MyClass.AccountNumber.ToString
End Get
blah blah
End Property
|
by: rengeek33 |
last post by:
I am building a SQL statement for Oracle and need one part of it to read:
, myvar = null, myvar2 = "1", myvar3 = "0", myvar4 = null,
etc.
I am constructing this string using the String object with the following code:
theSQL.Append(" , myvar = " & FormatValue(MyClass.DB_CHAR1, Me.MyVar))
| | |
by: Jeff |
last post by:
Hey
I've bought the book "ASP.NET 2.0 website programming, Problem, Design,
Solution" and some of its code examples is this code:
protected static string ConvertNullToEmptyString(string input)
{
return (input == null ? "" : input);
}
|
by: Micheal |
last post by:
Greetings Access Group,
Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.
I currently have a report that contains 2 totals and the percentage of the difference of the 2...
|
by: crabsdf |
last post by:
My project is a single method class which takes a xml object and, using Apache FOP, transforms it into a PDF which is returned as an output stream.
Here is full code
package transactionStatement;
import javax.xml.transform.TransformerFactory;
import javax.servlet.*;
import org.jdom.*;
import java.io.ByteArrayOutputStream;
import java.io.File;
|
by: kaka123 |
last post by:
hey, im doing this program and im reading the information from a file, i cant figure out how to get the print out right. Ive looked at all my for loops and i thought the error would be in the "substrings" that im doing..mabye the errror is in lines 25-39? this might be alost cause cause you cant run this file without the data file, but the scores and averages work, but the the peoples names dont print out right
it says
Null Null M 58 68 73...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
