Date projections

  • Thread starter Thread starter KevinS
  • Start date Start date
K

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.
 
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
 
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
 
Cancel this request. There is something wrong with my query since no matter
what I do there is 0 data.
Sorry!
 
You are very good! You answered what I could not figure out to ask.
Thank you,
Kevins
--
Is it the times or the Zeitgiest?


John Spencer MVP said:
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.
Click to expand...
Click to expand...
 
Back
Top