Calculate last 3 full months

[Guest]

This is probably pretty easy, but I've been busting my brain for the last 4
hours and can't figure it out!

I have a field called MonthYear from which the data is created through a
query by a formula =format([CreateDate],"mm-yyyy") so that all of the data
appears like 08-2006. In one instance, the results of this query is used in
a report to list ALL data no matter the date, but grouped by month and year.
This works fine. However, in another report, I want to use the query to list
ALL data for the previous full three months. In other words, if the report
were printed today, it would list all data for May, June, and July, but not
August since the month is not yet over. And this is my problem...I cannot
figure out what the statement should be to parse out all data other than the
previous full 3 months.

In the report record source, I am going through the SQL Statement : Query
Builder and have added the query. Now I am looking for something to put on
the Criteria line so that it will correctly parse out the dates I don't want.

Any suggestions?

Brian

 

[strive4peace]

Hi Brian,

use
DateAdd(interval, number, date)

and
DateSerial(year, month, day)

add this to your grid:

field --> CreateDate
show --> No (keep Yes until you have it working)
criteria --> BETWEEN DateAdd("m", -3,DateSerial(year(CreateDate),
month(CreateDate), 0)) AND DateSerial(year(date()), month(date()), 0)

if the date is already the last day of the month, you will need to tweak
the logic a bit, but hopefully this will get you on the path... if not,
post back



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Crystal,

Thanks for your help, we're almost there. The criteria has successfully
eliminated all of the records for the current month, but it didn't eliminate
any of the records prior to May 1, 2006.

Any more help you can give on this Friday afternoon would be great!

Thanks!
Brian

Hi Brian,

use
DateAdd(interval, number, date)

and
DateSerial(year, month, day)

add this to your grid:

field --> CreateDate
show --> No (keep Yes until you have it working)
criteria --> BETWEEN DateAdd("m", -3,DateSerial(year(CreateDate),
month(CreateDate), 0)) AND DateSerial(year(date()), month(date()), 0)

if the date is already the last day of the month, you will need to tweak
the logic a bit, but hopefully this will get you on the path... if not,
post back



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

This is probably pretty easy, but I've been busting my brain for the last 4
hours and can't figure it out!

I have a field called MonthYear from which the data is created through a
query by a formula =format([CreateDate],"mm-yyyy") so that all of the data
appears like 08-2006. In one instance, the results of this query is used in
a report to list ALL data no matter the date, but grouped by month and year.
This works fine. However, in another report, I want to use the query to list
ALL data for the previous full three months. In other words, if the report
were printed today, it would list all data for May, June, and July, but not
August since the month is not yet over. And this is my problem...I cannot
figure out what the statement should be to parse out all data other than the
previous full 3 months.

In the report record source, I am going through the SQL Statement : Query
Builder and have added the query. Now I am looking for something to put on
the Criteria line so that it will correctly parse out the dates I don't want.

Any suggestions?

Brian

 

[Guest]

Crystal,

Nevermind...I figured it out. I changed the criteria to read:

BETWEEN DateAdd("m", -3,DateSerial(year(Date()), month(Date()), 0)) AND
DateSerial(year(date()), month(date()), 0)

and it works great!

Thanks so much for your help!

Brian

Hi Brian,

use
DateAdd(interval, number, date)

and
DateSerial(year, month, day)

add this to your grid:

field --> CreateDate
show --> No (keep Yes until you have it working)
criteria --> BETWEEN DateAdd("m", -3,DateSerial(year(CreateDate),
month(CreateDate), 0)) AND DateSerial(year(date()), month(date()), 0)

if the date is already the last day of the month, you will need to tweak
the logic a bit, but hopefully this will get you on the path... if not,
post back



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

This is probably pretty easy, but I've been busting my brain for the last 4
hours and can't figure it out!

I have a field called MonthYear from which the data is created through a
query by a formula =format([CreateDate],"mm-yyyy") so that all of the data
appears like 08-2006. In one instance, the results of this query is used in
a report to list ALL data no matter the date, but grouped by month and year.
This works fine. However, in another report, I want to use the query to list
ALL data for the previous full three months. In other words, if the report
were printed today, it would list all data for May, June, and July, but not
August since the month is not yet over. And this is my problem...I cannot
figure out what the statement should be to parse out all data other than the
previous full 3 months.

In the report record source, I am going through the SQL Statement : Query
Builder and have added the query. Now I am looking for something to put on
the Criteria line so that it will correctly parse out the dates I don't want.

Any suggestions?

Brian

 

[strive4peace]

you're welcome, Brian happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal,

Nevermind...I figured it out. I changed the criteria to read:

BETWEEN DateAdd("m", -3,DateSerial(year(Date()), month(Date()), 0)) AND
DateSerial(year(date()), month(date()), 0)

and it works great!

Thanks so much for your help!

Brian

Hi Brian,

use
DateAdd(interval, number, date)

and
DateSerial(year, month, day)

add this to your grid:

field --> CreateDate
show --> No (keep Yes until you have it working)
criteria --> BETWEEN DateAdd("m", -3,DateSerial(year(CreateDate),
month(CreateDate), 0)) AND DateSerial(year(date()), month(date()), 0)

if the date is already the last day of the month, you will need to tweak
the logic a bit, but hopefully this will get you on the path... if not,
post back



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

This is probably pretty easy, but I've been busting my brain for the last 4
hours and can't figure it out!

I have a field called MonthYear from which the data is created through a
query by a formula =format([CreateDate],"mm-yyyy") so that all of the data
appears like 08-2006. In one instance, the results of this query is used in
a report to list ALL data no matter the date, but grouped by month and year.
This works fine. However, in another report, I want to use the query to list
ALL data for the previous full three months. In other words, if the report
were printed today, it would list all data for May, June, and July, but not
August since the month is not yet over. And this is my problem...I cannot
figure out what the statement should be to parse out all data other than the
previous full 3 months.

In the report record source, I am going through the SQL Statement : Query
Builder and have added the query. Now I am looking for something to put on
the Criteria line so that it will correctly parse out the dates I don't want.

Any suggestions?

Brian