Calculating dates using DateDiff

[dvn8r]

Hello all...
I have built a database to track various info about the personnel who work
for me. It is 95% done, but I have gotten stuck with one final report. I
need a report to show me which of my people have graduated a certain training
course 5 months prior to the report date or Date( ).
My query has the following field names from Table [Personnel Details]: Last
Name, First Name, Rank, SSN, Grad Date.
I have exhausted the help files and have searched the 'net for answers; even
the library turned up less technical data.
I am not familiar with writing SQL code so this is proving to be my
Achille's heel.

 

[Allen Browne]

Create a query based on this table.

In the Criteria row under the [Grad Date] field, enter:
DateAdd("m", 5, Date())

That will select the ones who graduated exactly 5 months ago. To include
those who graduated since then:

DateAdd("m", 5, Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all...
I have built a database to track various info about the personnel who work
for me. It is 95% done, but I have gotten stuck with one final report. I
need a report to show me which of my people have graduated a certain
training
course 5 months prior to the report date or Date( ).
My query has the following field names from Table [Personnel Details]:
Last
Name, First Name, Rank, SSN, Grad Date.
I have exhausted the help files and have searched the 'net for answers;
even
the library turned up less technical data.
I am not familiar with writing SQL code so this is proving to be my
Achille's heel.

 

[fredg]

Hello all...
I have built a database to track various info about the personnel who work
for me. It is 95% done, but I have gotten stuck with one final report. I
need a report to show me which of my people have graduated a certain training
course 5 months prior to the report date or Date( ).
My query has the following field names from Table [Personnel Details]: Last
Name, First Name, Rank, SSN, Grad Date.
I have exhausted the help files and have searched the 'net for answers; even
the library turned up less technical data.
I am not familiar with writing SQL code so this is proving to be my
Achille's heel.

Using DateDiff:
Select [Personal Details].* Form [Personal Details] where
DateDiff("m",[Personal Details].[GradDate],Date())>4

Using DateAdd:
Select ....
where [Personal Details].[GradDate] <= DateAdd("m",-5,[GradDate])

 

[fredg]

Hello all...
I have built a database to track various info about the personnel who work
for me. It is 95% done, but I have gotten stuck with one final report. I
need a report to show me which of my people have graduated a certain training
course 5 months prior to the report date or Date( ).
My query has the following field names from Table [Personnel Details]: Last
Name, First Name, Rank, SSN, Grad Date.
I have exhausted the help files and have searched the 'net for answers; even
the library turned up less technical data.
I am not familiar with writing SQL code so this is proving to be my
Achille's heel.

Using DateDiff:
Select [Personal Details].* Form [Personal Details] where
DateDiff("m",[Personal Details].[GradDate],Date())>4

Using DateAdd:
Select ....
where [Personal Details].[GradDate] <= DateAdd("m",-5,[GradDate])

I inadvertantly used [GradDate] in the DateAdd function in place of
Date().
That criteria should have read:
where [Personal Details].[GradDate]<=DateAdd("m",-5,Date())

To return records older than or equal to 5 months ago.

 

[Amy Blankenship]

I would think that would get people who WILL graduate in 5 months, and
people who will graduate in more than 5 months...?

Create a query based on this table.

In the Criteria row under the [Grad Date] field, enter:
DateAdd("m", 5, Date())

That will select the ones who graduated exactly 5 months ago. To include
those who graduated since then:

DateAdd("m", 5, Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all...
I have built a database to track various info about the personnel who
work
for me. It is 95% done, but I have gotten stuck with one final report.
I
need a report to show me which of my people have graduated a certain
training
course 5 months prior to the report date or Date( ).
My query has the following field names from Table [Personnel Details]:
Last
Name, First Name, Rank, SSN, Grad Date.
I have exhausted the help files and have searched the 'net for answers;
even
the library turned up less technical data.
I am not familiar with writing SQL code so this is proving to be my
Achille's heel.

 

[dvn8r]

Thank you all for your input...I could not get any of the expressions to
work; either they returned no results or incorrect results. After more
searching about, I found someone who knew how...the following expressions
performed my tasks....
DateDiff("m",Now(),[FMSS Grad Date])-5 This showed me all the records
with a grad date 5 months prior to now

DateDiff("m",Now(),[PRD])18 This one allowed me to calculate when
someone will be due to rotate in 18 months. I also have others to show 12
month window and 9 month window

Hope these help somoene else as well.

Again, thanks to all for your ideas.

Devin