MaxDate problem

  • Thread starter Thread starter Saylindara
  • Start date Start date
S

Saylindara

I have a list of mandatory courses that need to be updated at different
intervals . Over time the same course will be repeated but with a different
attendance date, e.g.
Date UpdateDue DateDue
(years)
Course A 22/3/06 2 22/3/08
Course A 17/4/07 2 17/4/09
Course A 3/5/08 2 3/5/10

I have a subform listing each course and I want another to show only the
overdue courses. I grouped the courses and used Max to get the last
attendance date. To select only the overdue courses I tried using
iif([DateDue]<Date(),"Overdue","") but "Overdue" appears against every course
overdue or not. Is that because the fields are grouped? How can I get round
this?
 
I was using the code in a query. Thank you for the Allen Browne link. I used
[CVDate([EventDate] + [UpdateFrequency]) which did everything I wanted except
that my UpdateFrequency is a year and not a day. How do I fix that?
 
Try

DateAdd("yyyy", 1, CVDate([EventDate])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Saylindara said:
I was using the code in a query. Thank you for the Allen Browne link. I
used
[CVDate([EventDate] + [UpdateFrequency]) which did everything I wanted
except
that my UpdateFrequency is a year and not a day. How do I fix that?


Linq Adams via AccessMonster.com said:
What exactly do you mean by "grouped?" You speak of a subform, but there
is
no grouping in forms, only in reports. Also, where are you trying to use
this
code? If you're using it in a query, you're going to have trouble because
of
your British date formatting.

For help on using this date formatting, have a look at Allen Browne's
International Dates in Access page at:

http://www.allenbrowne.com/ser-36.html

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
I do believe that works, thank you so much. I shall enter a bit more data in
just to make sure.
A month ago I didn't know a query from a crocus and I couldn't have got
this far without help from this site. I don't take it for granted.

Douglas J. Steele said:
Try

DateAdd("yyyy", 1, CVDate([EventDate])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Saylindara said:
I was using the code in a query. Thank you for the Allen Browne link. I
used
[CVDate([EventDate] + [UpdateFrequency]) which did everything I wanted
except
that my UpdateFrequency is a year and not a day. How do I fix that?


Linq Adams via AccessMonster.com said:
What exactly do you mean by "grouped?" You speak of a subform, but there
is
no grouping in forms, only in reports. Also, where are you trying to use
this
code? If you're using it in a query, you're going to have trouble because
of
your British date formatting.

For help on using this date formatting, have a look at Allen Browne's
International Dates in Access page at:

http://www.allenbrowne.com/ser-36.html

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top