SQL Logical Statement Problem
- From: "KCole" <KCole@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 28 Aug 2005 23:23:02 -0700
I have an Access database Query that assigns seniority to a person in an
organization based on when they started work (TIS Start Date). However, if
the database field for start date is blank, it automatically defaults the
seniority to the highest level. I'm trying to re-write the statement so that
it defaults to 0 seniority (less than one year at work), but can't seem to
get the query to do that. Can someone help? I tried to change the 20 at the
very end to 0, but that didn't work. Here's the current statement:
SELECT Personnel.[Service #], DateDiff("d",[TIS Start
Date],DateValue(Forms!payment!setmonth & "/28/" & Forms!payment!setyear)) AS
tis, [tis]/365 AS YoS, IIf([tis]<365,0,IIf([tis]>364 And
[tis]<730,1,IIf([tis]>729 And [tis]<1460,2,IIf([tis]>1459 And
[tis]<2190,4,IIf([tis]>2189 And [tis]<2920,6,IIf([tis]>2919 And
[tis]<3650,8,IIf([tis]>3649 And [tis]<5475,10,IIf([tis]>5474 And
[tis]<7300,15,20)))))))) AS payyears, Personnel.[TIS Start Date]
FROM Personnel
ORDER BY Personnel.[Service #];
.
- Prev by Date: ADO and SEEK Crash (VB6)
- Next by Date: Re: ADO RecordCount -1
- Previous by thread: ADO and SEEK Crash (VB6)
- Next by thread: C->ADO
- Index(es):
Relevant Pages
|
|
