Time as a Value in Queries

[Matt]

I am creating a query that requires to use dates (dates
in time) as a value for formulas.

Specifically I am trying calculate qualified vacation
time for employees by taking the more recent of the
employees start date or the beginning of the year.

eg. If todays date was 9/30/03 and the an employee had a
start date of 3/30/03 then they would qualify for 50% of
their alloted annual PTO, however if the employee started
on 5/15/01, they would qualify for 75% of the current
years PTO (because they would have been working the
current year since 1/1).

I would then take that % and multiply it by the number of
days alloted for the year. For example if an employee had
10 days of PTO for the year and qualified for 50% of
them, they would have 5 qualified days. From there I
could subtract their actual days off to calculate
remaining PTO.

The problem I'm facing is that I cannot figure out how to
convert dates into values like I can in excel. Any
advice?

 

[Duane Hookom]

I don't follow at all but perhaps I don't need to. Dates are stored as the
number of days since Dec 30, 1899. For instance:
CLng(Date()) = 37930

 

[John Spencer (MVP)]

How about?

DateDiff("d",IIF(HireDate<DateSerial(Year(Date()),1,1),
DateSerial(Year(Date()),1,1),
HireDate),Date())/365 * PTO