MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Date Query to create a report

• Follow

```I am a flight instructor with a flightschool in California.  I am trying to
set up a database for the school to help track when our instructors need to
recieve a new medical exam, flight review and annual proficency checks.  All
of these checks need to be done in a certain number of Calander months.  For
instance an instructor does their inital proficency check on May 1, 2009.  He
is required to have his next flight check no later than May 31, 2010.  Is
there a formula that I can use to request records a certain number of months
in the future as the acctual day of that month doesn't mater?  I don't care
if I have to add an extra field that is calculated off the original date
field, but I have no idea how to do it.  In the end, I want to create a
report that will tell me that an instructor's flight check needs to be done
this month or is overdue.  Anyone have an idea?
```
 0

```On Tue, 9 Mar 2010 22:44:01 -0800, CentralValleyPilot
<CentralValleyPilot@discussions.microsoft.com> wrote:

>  Is
>there a formula that I can use to request records a certain number of months
>in the future as the acctual day of that month doesn't mater?  I don't care
>if I have to add an extra field that is calculated off the original date
>field, but I have no idea how to do it.

You can use the DateSerial function for this. To get the first day of the
month twelve months after InitialDate you can use the DateSerial function. It
takes three arguments, a year, month, and day; but it's clever enough to take
the 17th month of this year and return the fifth month of next year. So you
could use an expression like

DateSerial(Year([initialdate]), Month([initialdate]) + 12, 1)

to return the first day of the month twelve months after the initial date
(e.g. for an Initialdate of #5/12/2009# this will return #5/1/2010#). To get
the LAST day of the month, add 13 and use the zeroth day:

DateSerial(Year([initialdate]), Month([initialdate]) + 13, 0)

>In the end, I want to create a
>report that will tell me that an instructor's flight check needs to be done
>this month or is overdue.  Anyone have an idea?

Uze a Query comparing the date calculated as above to Date().
--

John W. Vinson [MVP]
```
 0

1 Replies
176 Views

Similiar Articles:

7/28/2012 3:25:54 AM