calculation on a MAX(DATE) query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a query that returns the date of the most recent course taken by each
employee.

I am trying to subtract 2 years (730 days) from this date in the SELECT
statement. Why am I not allowed to do this, and more importantly how do I
overcome it?

(Given that I have already taken into account the possiblility of NULL
values with the NZ functionality.)

Any help would be appreciated.

Sincerely,
David
 
SELECT DATE_TRIAL.Emp_ID, Max(DATE_TRIAL.DATE_FIN) AS LAST_COURSE,
Nz(Max(DATE_TRIAL.DATE_FIN),0)-730 AS PAST
FROM DATE_TRIAL
GROUP BY DATE_TRIAL.Emp_ID;
 
What error message did you get? Try:
Field: Past:DateAdd("yyyy",-2,nz(Max([DATE_TRIAL.DATE_FIN]),0))
Total: Expression
 
Thank you that works. The error message was #ERROR for every row in that
column except for the rows where it converted a null date to 0. I just think
that that is really odd, because in the past the other SQL queries that is
how I have been manipulating dates without a problem. Is it just something
about using the MAX function?

I really appreciate your help.

Thanks again,
David

Duane Hookom said:
What error message did you get? Try:
Field: Past:DateAdd("yyyy",-2,nz(Max([DATE_TRIAL.DATE_FIN]),0))
Total: Expression

--
Duane Hookom
MS Access MVP
--

davidg2356 said:
SELECT DATE_TRIAL.Emp_ID, Max(DATE_TRIAL.DATE_FIN) AS LAST_COURSE,
Nz(Max(DATE_TRIAL.DATE_FIN),0)-730 AS PAST
FROM DATE_TRIAL
GROUP BY DATE_TRIAL.Emp_ID;
Click to expand...
Click to expand...
 
Not sure what the problem was/is. However, if you are using dates, I always
recommend using date type functions.

--
Duane Hookom
MS Access MVP
--

davidg2356 said:
Thank you that works. The error message was #ERROR for every row in that
column except for the rows where it converted a null date to 0. I just
think
that that is really odd, because in the past the other SQL queries that is
how I have been manipulating dates without a problem. Is it just
something
about using the MAX function?

I really appreciate your help.

Thanks again,
David

Duane Hookom said:
What error message did you get? Try:
Field: Past:DateAdd("yyyy",-2,nz(Max([DATE_TRIAL.DATE_FIN]),0))
Total: Expression

--
Duane Hookom
MS Access MVP
--

davidg2356 said:
SELECT DATE_TRIAL.Emp_ID, Max(DATE_TRIAL.DATE_FIN) AS LAST_COURSE,
Nz(Max(DATE_TRIAL.DATE_FIN),0)-730 AS PAST
FROM DATE_TRIAL
GROUP BY DATE_TRIAL.Emp_ID;

:

How about providing the sql view of "I have a query that..."?

--
Duane Hookom
MS Access MVP
--

Hello,

I have a query that returns the date of the most recent course taken
by
each
employee.

I am trying to subtract 2 years (730 days) from this date in the
SELECT
statement. Why am I not allowed to do this, and more importantly
how
do I
overcome it?

(Given that I have already taken into account the possiblility of
NULL
values with the NZ functionality.)

Any help would be appreciated.

Sincerely,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top