Null = 0 Problem

[That Crazy Hockey Dood]

Good Morning..

I have a question regarding my query and forcing null to show zero. Here is
the SQL:

SELECT Sum([EOSetups]+[FBOrders]+[NewAccounts]+[NCR]+[Admin
Activity_tbl]!OTML+[Pieces]+[ShowsPacked]+[ILSQuotes]+[INTLQuotes]+[DoNotExceed]+[Emails]+[Orders]+[NACallbacks]+[KeyAcctSales]+[Notes]+[FollowUp]+[ICSIndexed]+[Mail]+[CreditProcessing]+[IPSReturnabilities]+[ConsumerCatalogs]+[ReturnedMail]+[PRP]+[HeldOrders]+[VCommerce]+[QAEdits]+[QMEvaluations]+[IPSFax]+[IPSEmail]+[eBooks])
AS AdminTotal,
Sum([CallCenterCalls]+[OperationsCalls]+[AdministrationCalls]+[CostcoCalls]+[CSEmail]+[ILSEmail]+[INTLEmail]+[FaxOrders]+[EmlOrders]+[ILSFaxandEmailOrders]+[CDFEmail]+[CustCareEmail])
AS CallCenterTotal,
Sum([ProcessedTracers]+[OpsEmail]+[PIF]+[WIF]+[FaxedPL]+[Callbacks]+[CALetters]+[NoteCodeChanges]+[ShipdeskFU]+[NotesandFU]+[RSPONSEFU]+[FrtQuotes]+[HFForms]+[OSForms]+[CriticalCare]+[Returns]+[CCOPStoIC]+[PTOExpedited]+[PTOCancels]+[ACO]+[BNAirReq]+[SalesSupportCalls]+[SalesSupportEmail])
AS OpsTotal,
Sum([WorkOrders]+[BinderyShipments]+[BPNotes]+[FollowUps]+[EmailOrders]+[EmailCS]+[iPageOrders]+[TaschenYourPay]+[Allocations]+[IPSNewAcct])
AS PubCareTotal, tblPayroll.Payroll
FROM [Admin Activity_tbl], [Call Center Activity_tbl], CCOPS_tbl, [Pub
Care_tbl], tblPayroll
WHERE ((([Admin Activity_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND
(([Admin Activity_tbl].Year)=[Forms]![Monthly CPT]![Year]) AND (([Call Center
Activity_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Call Center
Activity_tbl].Year)=[Forms]![Monthly CPT]![Year]) AND
((CCOPS_tbl.Month)=[Forms]![Monthly CPT]![Month]) AND
((CCOPS_tbl.Year)=[Forms]![Monthly CPT]![Year]) AND (([Pub
Care_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Pub
Care_tbl].Year)=[Forms]![Monthly CPT]![Year]) AND
((tblPayroll.Month)=[Forms]![Monthly CPT]![Month]) AND
((tblPayroll.Year)=[Forms]![Monthly CPT]![Year]))
GROUP BY tblPayroll.Payroll;

I have tried several things like:
Nz(Sum([CallCenterCalls]+[OperationsCalls]+[AdministrationCalls]+[CostcoCalls]+[CSEmail]+[ILSEmail]+[INTLEmail]+[FaxOrders]+[EmlOrders]+[ILSFaxandEmailOrders]+[CDFEmail]+[CustCareEmail]),0)
and Sum(Nz([CallCenterCalls]),0)+etc.. However I am not able to get the
desired results. Am I missing something here?

Also, I wondered if I have gone the long way to get my results. This query
is triggered by a form which is run passing the selected month and year to
get the data for the text boxes on the form. The current query is a result
of trial and error.

Thanks in advance for your time and suggestions.

Jim

 

[Stefan Hoffmann]

hi,

I have a question regarding my query and forcing null to show zero.

Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[That Crazy Hockey Dood]

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!

hi,

I have a question regarding my query and forcing null to show zero.

Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

This should work with all kind of numbers, can you post your SQL you
have tried?

btw, you use a bang in your first sum: +[Admin Activity_tbl]!OTML


mfG
--> stefan <--

 

[That Crazy Hockey Dood]

Stefan..

Here is an example in short of the attempt:

SELECT
Sum(Nz([WorkOrders],0)+Nz([BinderyShipments],0)+Nz([BPNotes],0)+Nz([FollowUps],0)+Nz([EmailOrders],0)+Nz([EmailCS],0)+Nz([iPageOrders],0)+Nz([TaschenYourPay],0)+Nz([Allocations],0)+Nz([IPSNewAcct],0)) AS PubCareTotal
FROM [Pub Care_tbl]
WHERE ((([Pub Care_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Pub
Care_tbl].Year)=[Forms]![Monthly CPT]![Year]));

The result is an empty box.

--
If it works then you are doing something right!!

hi,

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

This should work with all kind of numbers, can you post your SQL you
have tried?

btw, you use a bang in your first sum: +[Admin Activity_tbl]!OTML


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Here is an example in short of the attempt:
SELECT
Sum(Nz([WorkOrders],0)+Nz([BinderyShipments],0)+Nz([BPNotes],0)+Nz([FollowUps],0)+Nz([EmailOrders],0)+Nz([EmailCS],0)+Nz([iPageOrders],0)+Nz([TaschenYourPay],0)+Nz([Allocations],0)+Nz([IPSNewAcct],0)) AS PubCareTotal
FROM [Pub Care_tbl]
WHERE ((([Pub Care_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Pub
Care_tbl].Year)=[Forms]![Monthly CPT]![Year]));

Looks fine to me. What does it return, when you drop the WHERE clause?

btw, Month and Year are reserved words, so you may also try a modified
WHERE:

WHERE [Month]=[Forms]![Monthly CPT]![Month]
AND [Year]=[Forms]![Monthly CPT]![Year];


mfG
--> stefan <--

 

[Dale Fye]

Chances are that the field is blank (contains an empty string) and not NULL.
Try:

Expr:iif(len([yourField] & "") = 0, 0, [yourField])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!

hi,

I have a question regarding my query and forcing null to show zero.

Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Dale,

Chances are that the field is blank (contains an empty string) and not NULL.
Try:
Expr:iif(len([yourField] & "") = 0, 0, [yourField])

Summing strings? oops, should have asked that...)



mfG
--> stefan <--

 

[Dale Fye]

Stefan,

My first though when NZ([YourField] , 0) didn't work was a string.

You would not expect this, but I have seen stranger things.

--
Dale

email address is invalid
Please reply to newsgroup only.

hi Dale,

Chances are that the field is blank (contains an empty string) and not NULL.
Try:
Expr:iif(len([yourField] & "") = 0, 0, [yourField])

Summing strings? oops, should have asked that...)



mfG
--> stefan <--

 

[Dale Fye]

Actually, if the field turns out to be a string, then maybe it should be:

Expr: iif(Len([yourField] & "") = 0, 0, val([yourField]))

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Chances are that the field is blank (contains an empty string) and not NULL.
Try:

Expr:iif(len([yourField] & "") = 0, 0, [yourField])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!

hi,

That Crazy Hockey Dood wrote:
I have a question regarding my query and forcing null to show zero.
Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[John W. Vinson]

Stefan..

Here is an example in short of the attempt:

SELECT
Sum(Nz([WorkOrders],0)+Nz([BinderyShipments],0)+Nz([BPNotes],0)+Nz([FollowUps],0)+Nz([EmailOrders],0)+Nz([EmailCS],0)+Nz([iPageOrders],0)+Nz([TaschenYourPay],0)+Nz([Allocations],0)+Nz([IPSNewAcct],0)) AS PubCareTotal
FROM [Pub Care_tbl]
WHERE ((([Pub Care_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Pub
Care_tbl].Year)=[Forms]![Monthly CPT]![Year]));

I'm not sure why you're getting a blank here but... it really looks like your
table structure is incorrect and needs normalization. You're "committing
spreadsheet", storing data in fieldnames. What are the datatypes and meanings
of these fields? It appears that you have a One (something... I don't know
what Entity is represented by this table) to Many relationship. You might want
to consider having *a second table*, with a foreign key link to the primary
key of this table, an ItemType field (containing "Work Order", "Bindery
Shipment", "FollowUp", etc. etc.), and a Number or Currency field containing
the data that you're now summing.

You should also certainly avoid the fieldnames Month and Year, and probably
actually store just a date/time field. This will make it easy to extract data
for a month... or a week, a quarter, or a year.

 

[That Crazy Hockey Dood]

Stefan & Dale..

Thank you for the input. I guess that I would agree with Dale's first
thought in the fact that a simple query using the Nz should work.
Ultimately, I am just telling the query to sum a recordset and provide that
total in the field outlined in my expression.

I am concerned that this is a lot of code to write for passing a 0 where the
value in the recordset is null (blank). Maybe I am better off leaving this
alone and just not report a 0 when the query finds there is no data to return.

I am now officially perplexed.
--
If it works then you are doing something right!!

Actually, if the field turns out to be a string, then maybe it should be:

Expr: iif(Len([yourField] & "") = 0, 0, val([yourField]))

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Chances are that the field is blank (contains an empty string) and not NULL.
Try:

Expr:iif(len([yourField] & "") = 0, 0, [yourField])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!


:

hi,

That Crazy Hockey Dood wrote:
I have a question regarding my query and forcing null to show zero.
Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[That Crazy Hockey Dood]

John..

I guess I can understand the confusion of what my intent is with the DB.

The tables are storing monthly figures of work completed in different area's
of the call center. For example, the different fields "WorkOrder",
BinderyShipment", etc. are tasks completed during a month by associates. The
information that resides in the recordset is recorded once a month. That is
the reason I elected to only use month and year b/c the date of entry would
never be consistent. This data was sitting in Excel at one point but was a
task to continue managing there. IMO, I like the ease of reporting on data
via Access.

I can easily go back and change Month and Year to Mth & Yr in the tables
while leaving the labels Month and Year. I know that Access does not like
the usage of reserved words.

Does this help you understand what I have and what I am trying to do?

Thanks,
Jim
--
If it works then you are doing something right!!

Stefan..

Here is an example in short of the attempt:

SELECT
Sum(Nz([WorkOrders],0)+Nz([BinderyShipments],0)+Nz([BPNotes],0)+Nz([FollowUps],0)+Nz([EmailOrders],0)+Nz([EmailCS],0)+Nz([iPageOrders],0)+Nz([TaschenYourPay],0)+Nz([Allocations],0)+Nz([IPSNewAcct],0)) AS PubCareTotal
FROM [Pub Care_tbl]
WHERE ((([Pub Care_tbl].Month)=[Forms]![Monthly CPT]![Month]) AND (([Pub
Care_tbl].Year)=[Forms]![Monthly CPT]![Year]));

I'm not sure why you're getting a blank here but... it really looks like your
table structure is incorrect and needs normalization. You're "committing
spreadsheet", storing data in fieldnames. What are the datatypes and meanings
of these fields? It appears that you have a One (something... I don't know
what Entity is represented by this table) to Many relationship. You might want
to consider having *a second table*, with a foreign key link to the primary
key of this table, an ItemType field (containing "Work Order", "Bindery
Shipment", "FollowUp", etc. etc.), and a Number or Currency field containing
the data that you're now summing.

You should also certainly avoid the fieldnames Month and Year, and probably
actually store just a date/time field. This will make it easy to extract data
for a month... or a week, a quarter, or a year.

 

[Dale Fye]

Dood,

You never really addressed the question, "are any of these fields text
fields?"

if any of the fields you are attempting to add together is NULL, then
summing them without the NZ( ) or IIF( ) wrapper will result in a NULL.

You indicated you had tried the NZ function before. Try this, create a
simple SELECT query that returns all the values, like:

SELECT [WorkOrders], [BinderyShipments],
[BPNotes], [FollowUps], [EmailOrders],
[EmailCS],[iPageOrders],
[TaschenYourPay], [Allocations],
[IPSNewAcct]
FROM [Pub Care_tbl]
WHERE [Pub Care_tbl].Month=[Forms]![Monthly CPT]![Month]
AND [Pub Care_tbl].Year=[Forms]![Monthly CPT]![Year]

Are any of these values empty? If so, modify it to:

SELECT NZ([WorkOrders],-99), NZ([BinderyShipments], -99),
NZ([BPNotes], -99), NZ([FollowUps], -99),
NZ([EmailOrders], -99), NZ([EmailCS], -99),
NZ([iPageOrders], -99), NZ([TaschenYourPay], -99),
NZ([Allocations],-99), NZ([IPSNewAcct], -99)
FROM [Pub Care_tbl]
WHERE [Pub Care_tbl].Month=[Forms]![Monthly CPT]![Month]
AND [Pub Care_tbl].Year=[Forms]![Monthly CPT]![Year]

What shows up in the "blank" cells now? ARe they still blank, or do they
now show -99. If they are "blank" or "empty", this is a good indication that
the field is a text field, not numeric. Text fields can be NULL, empty (""),
or contain a value. Numeric fields are either NULL or they contain a number.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Stefan & Dale..

Thank you for the input. I guess that I would agree with Dale's first
thought in the fact that a simple query using the Nz should work.
Ultimately, I am just telling the query to sum a recordset and provide that
total in the field outlined in my expression.

I am concerned that this is a lot of code to write for passing a 0 where the
value in the recordset is null (blank). Maybe I am better off leaving this
alone and just not report a 0 when the query finds there is no data to return.

I am now officially perplexed.
--
If it works then you are doing something right!!

Actually, if the field turns out to be a string, then maybe it should be:

Expr: iif(Len([yourField] & "") = 0, 0, val([yourField]))

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Chances are that the field is blank (contains an empty string) and not NULL.
Try:

Expr:iif(len([yourField] & "") = 0, 0, [yourField])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!


:

hi,

That Crazy Hockey Dood wrote:
I have a question regarding my query and forcing null to show zero.
Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--

 

[John W. Vinson]

John..

I guess I can understand the confusion of what my intent is with the DB.

The tables are storing monthly figures of work completed in different area's
of the call center. For example, the different fields "WorkOrder",
BinderyShipment", etc. are tasks completed during a month by associates. The
information that resides in the recordset is recorded once a month. That is
the reason I elected to only use month and year b/c the date of entry would
never be consistent. This data was sitting in Excel at one point but was a
task to continue managing there. IMO, I like the ease of reporting on data
via Access.

I can easily go back and change Month and Year to Mth & Yr in the tables
while leaving the labels Month and Year. I know that Access does not like
the usage of reserved words.

Does this help you understand what I have and what I am trying to do?

Somewhat. But I can certainly see that the Excel history is still affecting
the structure of your database. It's a good spreadsheet... but a BAD table!

I'd suggest splitting this up into multiple tables:

Associates
AssociateID <Primary Key>
LastName
FirstName
<biographical and contact information as needed>

WorkOrders
WOID <Autonumber Primary Key>
WorkOrderDate <Date/Time, it needn't be the date it was entered, you can use
the DateSerial function to let the user type in a year and month and fill in
the first day of that month>
AssociateID

Tasks
TaskID <autonumber primary key>
Task <e.g. "Bindery Shipment", "Follow Up", "EMail Order">

WorkOrderDetails
WOID <link to Workorders>
TaskID <link to Tasks>
HoursWorked

If one associate did six tasks in a month, there'd be six records for that
associate during that month in WorkOrderDetails; if they did nine tasks the
next month, there'd be nine, and so on.

With your design, if you have to add or change a task, you must restructure
your table, change all your queries, change all your reports, rebuild your
data entry form... with the normalized design you add a new row in Tasks and
you're done.

 

[That Crazy Hockey Dood]

Dale..

I apologize. In my frenzy of typing I missed responding to your base
question. The primary tables involved in my queries are in fact 2 text boxes
(Mth & Yr). I just realized in typing that I could trim to 1 txt box. Not
only that I just believe I had another thought. I could technically have the
record write to the table a ID number for month and an ID number for year.
Could I not? Would that likely resolve some of my issues?

The results were blank on both test queries you provided.

Thanks,
Jim
--
If it works then you are doing something right!!

Dood,

You never really addressed the question, "are any of these fields text
fields?"

if any of the fields you are attempting to add together is NULL, then
summing them without the NZ( ) or IIF( ) wrapper will result in a NULL.

You indicated you had tried the NZ function before. Try this, create a
simple SELECT query that returns all the values, like:

SELECT [WorkOrders], [BinderyShipments],
[BPNotes], [FollowUps], [EmailOrders],
[EmailCS],[iPageOrders],
[TaschenYourPay], [Allocations],
[IPSNewAcct]
FROM [Pub Care_tbl]
WHERE [Pub Care_tbl].Month=[Forms]![Monthly CPT]![Month]
AND [Pub Care_tbl].Year=[Forms]![Monthly CPT]![Year]

Are any of these values empty? If so, modify it to:

SELECT NZ([WorkOrders],-99), NZ([BinderyShipments], -99),
NZ([BPNotes], -99), NZ([FollowUps], -99),
NZ([EmailOrders], -99), NZ([EmailCS], -99),
NZ([iPageOrders], -99), NZ([TaschenYourPay], -99),
NZ([Allocations],-99), NZ([IPSNewAcct], -99)
FROM [Pub Care_tbl]
WHERE [Pub Care_tbl].Month=[Forms]![Monthly CPT]![Month]
AND [Pub Care_tbl].Year=[Forms]![Monthly CPT]![Year]

What shows up in the "blank" cells now? ARe they still blank, or do they
now show -99. If they are "blank" or "empty", this is a good indication that
the field is a text field, not numeric. Text fields can be NULL, empty (""),
or contain a value. Numeric fields are either NULL or they contain a number.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Stefan & Dale..

Thank you for the input. I guess that I would agree with Dale's first
thought in the fact that a simple query using the Nz should work.
Ultimately, I am just telling the query to sum a recordset and provide that
total in the field outlined in my expression.

I am concerned that this is a lot of code to write for passing a 0 where the
value in the recordset is null (blank). Maybe I am better off leaving this
alone and just not report a 0 when the query finds there is no data to return.

I am now officially perplexed.
--
If it works then you are doing something right!!

Actually, if the field turns out to be a string, then maybe it should be:

Expr: iif(Len([yourField] & "") = 0, 0, val([yourField]))

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Chances are that the field is blank (contains an empty string) and not NULL.
Try:

Expr:iif(len([yourField] & "") = 0, 0, [yourField])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Stefan..

Thank you for your quick response. I had tried this before and it did not
work. I tried again to ensure I did not miss something on a previous
attempt. Unfortunately, the result displayed was a blank opposed to giving
me a 0.

Jim
--
If it works then you are doing something right!!


:

hi,

That Crazy Hockey Dood wrote:
I have a question regarding my query and forcing null to show zero.
Use

Nz([yourField1], 0) + Nz([yourField2], 0) + ...


mfG
--> stefan <--