query based question..... null value?

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

hi, i have a table , here are the 5 fields:
with example data shown

LNAME FNAME DOB STARTDATE PRIORSERVICE
smith john 11/9/73 12/01/03 12/01/01
king tom 06/07/70 2/05/01


i have a query set up off of this data that calculates
their age and total years of service

the query is set up like this with these 7 fields:
LNAME

FNAME

DOB

STARTDATE

PRIORSERVICE

Age: Fix(DateDiff("yyyy",[DOB],Now())+Int(Format(Now
(),"mmdd")<Format([DOB],"mmdd")))

TotalYrsService: Fix(DateDiff("yyyy",[PriorService],Now())
+Int(Format(Now(),"mmdd")<Format([PriorService],"mmdd")))


***** my problem is...
it works great if the person has a priorservice date
but if they do not... then it doesn't calculate both their
age and totalyrsservice.. it only calculates the age...
for that record

example of what the query would look like if ran.. with
the above information in the table:

LNAME FNAME DOB STARTDATE PRIORSERVICE AGE TOTALYRS
smith john 11/9/73 12/01/03 12/01/01 30 3
king tom 06/07/70 2/05/01 33


Any ideas will be greatly appreciated :) :) :) :)

(i abbreviated that field name for TOTALYRSSERVICE in that
last example query so it would all appear on one line
above)

Thanks
Aaron
(e-mail address removed)
 
Well, to put it another way, you're saying....

2 + Nothing = ???

Nothing can compute this cause it doesn't make sense. Take a look at what
TotalYearsOfService really is trying to communicate. If there is no prior
service date, and that implies that no years of service have been rendered,
then put something like an IIF() statement in your expression saying...

IIF(IsNull(ServiceDate), 0, <your normal calculation for years of service if
the service data is not null>)

Casey
 
i got it ( i did explain it all correctly... i guess)
my formula for TOTALYRSSERVICE is this

TotalYrsService: IIf(IsNull([PriorService]),Fix(DateDiff
("yyyy",[StartDate],Now())+Int(Format(Now(),"mmdd")<Format
([StartDate],"mmdd"))),Fix(DateDiff("yyyy",
[PriorService],Now())+Int(Format(Now(),"mmdd")<Format
([PriorService],"mmdd"))))

thanks for steering me in the correct direction
very appreciative :)
-Aaron

Hi Casey,

i just read your reply.. to my newsgroup post
sorry... to be so dumb... but i know what youre saying..
this thing has just been driving me nuts... i'm new to
access

anyway...
how i'd like it to work would be
if that priorservice is ZERO, then i want it just to take
the STARTDATE and today's current DATE and find the
difference....
is that what your IIF statement is?

IIF(IsNull(ServiceDate), 0, <your normal calculation for
years of service if
the service data is not null>)
-----Original Message-----
Well, to put it another way, you're saying....

2 + Nothing = ???

Nothing can compute this cause it doesn't make sense. Take a look at what
TotalYearsOfService really is trying to communicate. If there is no prior
service date, and that implies that no years of service have been rendered,
then put something like an IIF() statement in your expression saying...

IIF(IsNull(ServiceDate), 0, <your normal calculation for years of service if
the service data is not null>)

Casey

Aaron said:
hi, i have a table , here are the 5 fields:
with example data shown

LNAME FNAME DOB STARTDATE PRIORSERVICE
smith john 11/9/73 12/01/03 12/01/01
king tom 06/07/70 2/05/01


i have a query set up off of this data that calculates
their age and total years of service

the query is set up like this with these 7 fields:
LNAME

FNAME

DOB

STARTDATE

PRIORSERVICE

Age: Fix(DateDiff("yyyy",[DOB],Now())+Int(Format(Now
(),"mmdd")<Format([DOB],"mmdd")))

TotalYrsService: Fix(DateDiff("yyyy",[PriorService],Now ())
+Int(Format(Now(),"mmdd")<Format ([PriorService],"mmdd")))


***** my problem is...
it works great if the person has a priorservice date
but if they do not... then it doesn't calculate both their
age and totalyrsservice.. it only calculates the age...
for that record

example of what the query would look like if ran.. with
the above information in the table:

LNAME FNAME DOB STARTDATE PRIORSERVICE AGE TOTALYRS
smith john 11/9/73 12/01/03 12/01/01 30 3
king tom 06/07/70 2/05/01 33


Any ideas will be greatly appreciated :) :) :) :)

(i abbreviated that field name for TOTALYRSSERVICE in that
last example query so it would all appear on one line
above)

Thanks
Aaron
(e-mail address removed)
Click to expand...


.
Click to expand...
 
still isn't right...
let me look at this again...
-----Original Message-----
i got it ( i did explain it all correctly... i guess)
my formula for TOTALYRSSERVICE is this

TotalYrsService: IIf(IsNull([PriorService]),Fix(DateDiff
("yyyy",[StartDate],Now())+Int(Format(Now(),"mmdd")<Format
([StartDate],"mmdd"))),Fix(DateDiff("yyyy",
[PriorService],Now())+Int(Format(Now(),"mmdd")<Format
([PriorService],"mmdd"))))

thanks for steering me in the correct direction
very appreciative :)
-Aaron

Hi Casey,

i just read your reply.. to my newsgroup post
sorry... to be so dumb... but i know what youre saying..
this thing has just been driving me nuts... i'm new to
access

anyway...
how i'd like it to work would be
if that priorservice is ZERO, then i want it just to take
the STARTDATE and today's current DATE and find the
difference....
is that what your IIF statement is?

IIF(IsNull(ServiceDate), 0, <your normal calculation for
years of service if
the service data is not null>)
-----Original Message-----
Well, to put it another way, you're saying....

2 + Nothing = ???

Nothing can compute this cause it doesn't make sense. Take a look at what
TotalYearsOfService really is trying to communicate. If there is no prior
service date, and that implies that no years of service have been rendered,
then put something like an IIF() statement in your expression saying...

IIF(IsNull(ServiceDate), 0, <your normal calculation for years of service if
the service data is not null>)

Casey

Aaron said:
hi, i have a table , here are the 5 fields:
with example data shown

LNAME FNAME DOB STARTDATE PRIORSERVICE
smith john 11/9/73 12/01/03 12/01/01
king tom 06/07/70 2/05/01


i have a query set up off of this data that calculates
their age and total years of service

the query is set up like this with these 7 fields:
LNAME

FNAME

DOB

STARTDATE

PRIORSERVICE

Age: Fix(DateDiff("yyyy",[DOB],Now())+Int(Format(Now
(),"mmdd")<Format([DOB],"mmdd")))

TotalYrsService: Fix(DateDiff("yyyy",[PriorService],Now ())
+Int(Format(Now(),"mmdd")<Format ([PriorService],"mmdd")))


***** my problem is...
it works great if the person has a priorservice date
but if they do not... then it doesn't calculate both their
age and totalyrsservice.. it only calculates the age...
for that record

example of what the query would look like if ran.. with
the above information in the table:

LNAME FNAME DOB STARTDATE PRIORSERVICE AGE TOTALYRS
smith john 11/9/73 12/01/03 12/01/01 30 3
king tom 06/07/70 2/05/01 33


Any ideas will be greatly appreciated :) :) :) :)

(i abbreviated that field name for TOTALYRSSERVICE in that
last example query so it would all appear on one line
above)

Thanks
Aaron
(e-mail address removed)
Click to expand...


.
Click to expand...
.
Click to expand...
 
ok.... i think i've got it now
my formula for total years of service ended up being:

TotalYrsService: IIf(IsNull([PriorService]),Fix(DateDiff
("yyyy",[StartDate],Now())+Int(Format(Now(),"mmdd")<Format
([StartDate],"mmdd"))),Fix(DateDiff("yyyy",
[PriorService],Now())+Int(Format(Now(),"mmdd")<Format
([PriorService],"mmdd"))))



-----Original Message-----
i got it ( i did explain it all correctly... i guess)
my formula for TOTALYRSSERVICE is this

TotalYrsService: IIf(IsNull([PriorService]),Fix(DateDiff
("yyyy",[StartDate],Now())+Int(Format(Now(),"mmdd")<Format
([StartDate],"mmdd"))),Fix(DateDiff("yyyy",
[PriorService],Now())+Int(Format(Now(),"mmdd")<Format
([PriorService],"mmdd"))))

thanks for steering me in the correct direction
very appreciative :)
-Aaron

Hi Casey,

i just read your reply.. to my newsgroup post
sorry... to be so dumb... but i know what youre saying..
this thing has just been driving me nuts... i'm new to
access

anyway...
how i'd like it to work would be
if that priorservice is ZERO, then i want it just to take
the STARTDATE and today's current DATE and find the
difference....
is that what your IIF statement is?

IIF(IsNull(ServiceDate), 0, <your normal calculation for
years of service if
the service data is not null>)
-----Original Message-----
Well, to put it another way, you're saying....

2 + Nothing = ???

Nothing can compute this cause it doesn't make sense. Take a look at what
TotalYearsOfService really is trying to communicate. If there is no prior
service date, and that implies that no years of service have been rendered,
then put something like an IIF() statement in your expression saying...

IIF(IsNull(ServiceDate), 0, <your normal calculation for years of service if
the service data is not null>)

Casey

Aaron said:
hi, i have a table , here are the 5 fields:
with example data shown

LNAME FNAME DOB STARTDATE PRIORSERVICE
smith john 11/9/73 12/01/03 12/01/01
king tom 06/07/70 2/05/01


i have a query set up off of this data that calculates
their age and total years of service

the query is set up like this with these 7 fields:
LNAME

FNAME

DOB

STARTDATE

PRIORSERVICE

Age: Fix(DateDiff("yyyy",[DOB],Now())+Int(Format(Now
(),"mmdd")<Format([DOB],"mmdd")))

TotalYrsService: Fix(DateDiff("yyyy",[PriorService],Now ())
+Int(Format(Now(),"mmdd")<Format ([PriorService],"mmdd")))


***** my problem is...
it works great if the person has a priorservice date
but if they do not... then it doesn't calculate both their
age and totalyrsservice.. it only calculates the age...
for that record

example of what the query would look like if ran.. with
the above information in the table:

LNAME FNAME DOB STARTDATE PRIORSERVICE AGE TOTALYRS
smith john 11/9/73 12/01/03 12/01/01 30 3
king tom 06/07/70 2/05/01 33


Any ideas will be greatly appreciated :) :) :) :)

(i abbreviated that field name for TOTALYRSSERVICE in that
last example query so it would all appear on one line
above)

Thanks
Aaron
(e-mail address removed)
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top