1. Registered User
Join Date
Jun 2014
Location
In a deep fog...
Posts
11

This is a weird one. I have a report based on a query that has 5 controls. In the query, each control is a count of a number of events that occurs that day. If there is no count, I have forced the control to be a zero using the NZ() statement in SQL.

In the report, I display all 5 controls in a column and then have another control to provide the sum, i.e. = cntr1 + cntr2 + cntr3 + cntr4 + cntr5.

When there is a number other than zero in at least one of the controls, it sums correctly. However, when all controls are zero, then the sum control displays 00000 instead of 0.

Anyone have any ideas on this?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
any chance of seeing the actual query. diagnising what is going wrong in such matters by description alone is virtually impossible

what value are you supplying in the NZ function. I'd hazard a guess that you are using
nz(mycolumn, "0") rather than nz(mycolumn, 0)

do you actually mean you are summing controls, as opposed to columns. controls are used in forms and reports, columns are the underlying values in the table.

can we see the original sum or count query aswell, as Im surprised you have to consider using the NZ fucntion as the retrun of a SQL select count where there are no values int he result set is (usually) 0

3. Registered User
Join Date
Jun 2014
Location
In a deep fog...
Posts
11
The SQL code for the query is as follows;

Code:
```SELECT Nz(Sum(IIf([Movement Stats Table].[Phase 1 Sortie]=True,1,0)),0) AS [Total Phase 1 Sorties], Nz(Sum(IIf([Movement Stats Table].[Phase 2 Sortie]=True,1,0)),0) AS [Total Phase 2 Sorties], Nz(Sum(IIf([Movement Stats Table].[Phase 3 ME Sortie]=True,1,0)),0) AS [Total BE9L Sorties], Nz(Sum(IIf([Movement Stats Table].[Phase 3 BH Sortie]=True,1,0)),0) AS [Total B06 Sorties], Nz(Sum(IIf([Movement Stats Table].[Phase 3 AH Sortie]=True,1,0)),0) AS [Total B412 Sorties], Nz(Sum(IIf([Movement Stats Table].[Type of Flight]="X",1,0)+IIf([Movement Stats Table].[Type of Flight]="Y",1,0)+IIf([Movement Stats Table].[Type of Flight]="Z",1,0)),0) AS [Total IFR]
FROM [Movement Stats Table]
WHERE ((([Movement Stats Table].[Date])=FORMS!OpenCloseTimesForm!OpenDate) And (([Movement Stats Table].[Arrive Time])>#12/30/1899 12:0:0# And ([Movement Stats Table].[Arrive Time])<#12/30/1899 23:59:0#) And (([Movement Stats Table].[Depart Time])>#12/30/1899 12:0:0# And ([Movement Stats Table].[Depart Time])<#12/30/1899 23:59:0#));```
So, in the report, I display Phase 1 Sorties, Phase 2 Sorties, etc in a column. At the bottom of the column, I have another control that sums these. Before I put the NZ() piece in the query, if there was nothing to count then the report would display a blank box instead of 0 and the sum box would also be blank. This is my attempt to force the report to display the zeros.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
AS an aside, when designing tables dont' use spaces (or other reserved words or symbols). also column and table names should be brief but descriptive. if you want a human friendly name then set the columns 'caption' property. that caption wiull then be pulled into forms and reports you design subsequently based on those columns. being to verbose with column or table names can cause issues down the line (some SQL engines only look at the first 32 characters, some SQL engines apply a limit on the number of characters in a query (although I doubt you will hit that limit in Access / JET SQL).

Im not to sure what you are trying to express with the time bandings (arrival and departure time. Im hoping you are not going to tell me you have stored the date and time as separate elements. assumign you have stroed the values in a datetiem variable then use the the appropriate date tim,e functions (eg HOUR() and MINUTE().

there's various ways to tackle this. one is to split it into 2 querieis, the first query assigns 1 or 0 dependign on sepcific column values
eg:-
SELECT
IIf([Movement Stats Table].[Phase 1 Sortie]=True,1,0)),0) AS Phase1,
IIf([Movement Stats Table].[Phase 2 Sortie]=True,1,0)),0) AS Phase2,
IIf([Movement Stats Table].[Phase 3 ME Sortie]=True,1,0)),0) AS BE9L,
IIf([Movement Stats Table].[Phase 3 BH Sortie]=True,1,0)),0) AS B06,
IIf([Movement Stats Table].[Phase 3 AH Sortie]=True,1,0)),0) AS B412,
IIf([Movement Stats Table].[Type of Flight]="X" or [Movement Stats Table].[Type of Flight]="Y" or IIf([Movement Stats Table].[Type of Flight]="Z",1,0) AS [IFR]
FROM [Movement Stats Table]
..use the same where clause
then run a summarise query on that query
select sum(Phase1), sum(Phase2).......

you could short cut that by taking advantage of an implementation detail of JET SWQL, where true is stored internally as -1, False is 0 and NULL (effectivley) unspecified and do it ion one query
select ABS(sum([Phase 1 Sortie])) as SumPhase1......

thr ABS is required to convert -1 to 1

5. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Did you try setting a numeric format for the unbound textbox? This might keep Access from being confused as to what it's supposed to do.

6. Registered User
Join Date
Jun 2014
Location
In a deep fog...
Posts
11
healdem - This database is way too far down the road to fix my initial naming conventions. As I build new stuff into it, I'm correcting that process.

The problem I run into is that if there was no data entered on that day, then the query would have null values. In this case the report shows blank controls instead of the desired 0. That's when I tried the Nz() function. However, with the Nz() function added, I get the 0 in the correct controls but now the unbound textbox concatenates instead of adding.

I tried the ABS function, but it returns Null as a blank taking me back to my original problem.

I also tried linq's suggestion of setting the format to General number and Standard number, but this didn't change the textbox. It still read 00000.

I have since discovered that the unbound textbox is concatenating even when the numbers are >0. So my last two days of reports have been wrong.

At the moment, I'm reverting to just having the report have blank (null) cells. Not as pretty, but correct.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
make life simpler for yourself. you don't have to do everything in the query. if you move on to using server DB/s and or develop 'middle ware' its expected (if not required) that some of the processing should be done away from the db server. its a good habit to get into early

do the counting/ summation in the query

then when you use those values in a form or report use the NZ function to cater for NULL aggergate values. its easy to confuse data manipualtion and presentation when designign stuff in Access (or Filemaker and similar RAD packages)

8. Registered User
Join Date
Jun 2014
Location
In a deep fog...
Posts
11
This report was created based on this query. There are about 11 subforms on the report (hidden) that allow me to display a lot of other query-generated information. I tried to simply go to the control and use the Nz() function in the calculated control field properties like so:

=Nz([Total Phase 1 Sorties],0)

This works for all the controls that look at the subforms, but not for the controls for this particular query. I get a #Size! error when I try to use it this way.

I'm not sure what you mean by "move on to using server DB/s and or develop 'middle ware'" My DB is only used on one computer and I haven't split it into front-end & back-end.

9. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Given that you've tried Formatting the Unbound Textbox to General Number/Standard Number, and it still shows 00000, which indicates that it is considering the data as Text, I wonder if the problem is that the Textbox Control has become corrupted.

Although we usually think of Forms, or even entire Databases, when we speak of corruption, individual Controls, such as your Textbox, can and do become corrupted! The test and the fix are one and the same; deleting the Control then re-creating it! If it was corrupt, you've verified the fact and solved the problem! If not, you've only lost a minute or two!

Linq ;0)>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•