Date projections

[KevinS]

In my database is a certification field that should be less than a year old.
I would like to project which certifications are going to expire in the next
3 months.

I copied this from a previous posting and tried >=Date() And
<DateAdd("d",Date(),90) but I got 0 results. Do i need to replace something?

Thank you.

 

[vanderghast]

In the query design grid, as criteria, under the date field? that should
work, unless you date field is not a date, but, say, a string.


Do you have records if you just type: >= Date()

Is your PC date (clock) is right, or set in another month, another year?

If you switch in SQL view, can you spot the WHERE clause with:

dateFieldName >= Date() AND dateFieldName < DateAdd("d",Date(),90)

and if so, is there something ELSE in the WHERE clause, or can you post the
entire SQL statement you have, in SQL view?



Vanderghast, Access MVP

 

[John Spencer MVP]

Well if you are searching for records that will expire based on the
certification date and the certification date is in the past I think your
criteria would be

Between DateAdd("m",-12,Date()) and DateAdd("m",-9,Date())

That should get records with a certification date between one year ago and 9
months ago. Those would be the ones that are due to expire in the next 3 months.

Optionally, you could add 1 year to the current certification dates and check
with your criteria.

Field: DateAdd("yyyy",1,[CertificationDate])
Criteria: >=Date() And < DateAdd("d",90,Date())

And any Certification that was more than one year ago would not show up. So
you might want to look for overdue certifications with criteria like
<DateAdd("m",-12,Date())

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KevinS]

Cancel this request. There is something wrong with my query since no matter
what I do there is 0 data.
Sorry!

 

[KevinS]

You are very good! You answered what I could not figure out to ask.
Thank you,
Kevins
--
Is it the times or the Zeitgiest?

Well if you are searching for records that will expire based on the
certification date and the certification date is in the past I think your
criteria would be

Between DateAdd("m",-12,Date()) and DateAdd("m",-9,Date())

That should get records with a certification date between one year ago and 9
months ago. Those would be the ones that are due to expire in the next 3 months.

Optionally, you could add 1 year to the current certification dates and check
with your criteria.

Field: DateAdd("yyyy",1,[CertificationDate])
Criteria: >=Date() And < DateAdd("d",90,Date())

And any Certification that was more than one year ago would not show up. So
you might want to look for overdue certifications with criteria like
<DateAdd("m",-12,Date())

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

In my database is a certification field that should be less than a year old.
I would like to project which certifications are going to expire in the next
3 months.

I copied this from a previous posting and tried >=Date() And
<DateAdd("d",Date(),90) but I got 0 results. Do i need to replace something?

Thank you.