If no records, value = 0

[Walter Steadman]

I am working on my Aviator Database and I have the query set up to show me
how many hours between two certain dates a pilot has flown. It works great
BUT if a pilot has not flown any hours then he/she is not included in the
Query. Is there a way to have the query list all the pilots and for the
ones that there is no time have it put a 0?

I am working on two 6 month time periods during a year. The time periods
are as follows:

Period 1 is from the 1st of the Month following the persons birth month to
the 31st of the Month 6 months later (example: Birthdate is in Feb so time
period will be 1 Mar through 31 Aug). I am running a query to find
calculate time between those two dates and then another query to calculate
time outside those two dates (this will make up the total for the 2nd
period).

LASTNAME FIRSTNAME BIRTH MONTH JAN FEB MAR APR MAY JUN JUL AUG SEP OCT
NOV DEC
JONES DAVID MAR 8 8 2
SMITH STEVE NOV 2
GILES FRANK APR 6 5
LEWIS DANA OCT 8 8 8 8 6 2 3 9
STEINMAN LINDA DEC 8 8 8 8 8 8
WELLS FAYE JAN 8 7 9 10 2
FINNIE EDWARD JUL 8 8 8 8 2
WARD AMBER APR 10 2
FAULKNER WALTER JUL 8 8 8 8 8 2 8 8 8 8 8

TIME PERIOD 1
TIME PERIOD 2


So for Finnie (7th on list) his TIME PERIOD 1 would not reflect any hours
flown because he has not even begun this time period but his Time period 2
would contain 34 hours

But I need Finnie to show up in the answer to the query so when I query for
Time Period two, they will add in as well.

My report will look like:

NAME BIRTH MONTH TP 1 TP 2
FINNIE JUL 0 43
JONES MAR 18 0
SMITH NOV 0 2

ETC.....

I archive the dates that are greater than 6 months from their 1st TIME
PERIOD date, so there should never be an issue with calculating dates based
on the year

Any help in this would certainly be appreciated.

TIA
Wally Steadman
US Army in Iraq

 

[Ted Allen]

Hi Wally,

I had a bit of a hard time following the exact process that you are using to
tie all of this data together, but it is probably likely that just putting
your formulas inside the nz() function will resolve the problem.

So, in place of your current formula, use:

nz(YourFormulaHere,0)

This will substitute 0's for cases where the formula returns Null.

There are probably many ways to do what you want to do, but this may be the
quickest fix. If this doesn't work, post back with a little more info on the
exact method that you are using to calculate these formulas. It sounds like
you are using a series of queries. But, even in that case, the queries can
return values for all pilots even when the formulas are null if they are
written to do so. Perhaps posting the sql text of your queries would clarify
what is going on.

HTH, Ted Allen

 

[Pendragon]

Try this:

iif(isnull([myfield]),0,[myfield])