Date Criteria

[Guest]

I would like a query that reports all forecast dates for the current month,
along with all forecast dates from the previous and following month. E.g.,
for the month of March, I want a report on February, March and April. I know
how to request the months specifically, but I would like something generic so
I don't have to change the dates in the query criteria field every month. As
I have it set up now, I only get a report that gives me forecast dates 30
days plus and 30 days minus the current date. I'm using the following syntax:

Between Date( ) And DateAdd("M", 1, Date( )) OR
Between Date( ) And DateAdd("M", -1, Date( ))

I would use...

Year([ForecastDate]) = Year(Now( )) And Month ([ForecastDate]) = Month(Now( ))

....but this only gives me the current month, and I don't know how to work it
to give me next month and last month's dates.

Is there a way that I can get all of the current month, previous month and
following month in one report?

 

[fredg]

I would like a query that reports all forecast dates for the current month,
along with all forecast dates from the previous and following month. E.g.,
for the month of March, I want a report on February, March and April. I know
how to request the months specifically, but I would like something generic so
I don't have to change the dates in the query criteria field every month. As
I have it set up now, I only get a report that gives me forecast dates 30
days plus and 30 days minus the current date. I'm using the following syntax:

Between Date( ) And DateAdd("M", 1, Date( )) OR
Between Date( ) And DateAdd("M", -1, Date( ))

I would use...

Year([ForecastDate]) = Year(Now( )) And Month ([ForecastDate]) = Month(Now( ))

...but this only gives me the current month, and I don't know how to work it
to give me next month and last month's dates.

Is there a way that I can get all of the current month, previous month and
following month in one report?

Between DateAdd("m",-1,Date()) and DateAdd("m",1,Date())

This will give 3 months to the day you run the query, i.e.
2-16-2005 thru 4-16-2005

If you want from the first of the previous month to the last of the
following month, regardless of the actual day the query is run, try:
Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+2,0)

2/1/2005 thru 4/30/2005

 

[Guest]

The string you gave me resulted in all march and april dates, but no february
dates. Can you see where the criteria may be missing the necessary pointer
for the previous month?

I would like a query that reports all forecast dates for the current month,
along with all forecast dates from the previous and following month. E.g.,
for the month of March, I want a report on February, March and April. I know
how to request the months specifically, but I would like something generic so
I don't have to change the dates in the query criteria field every month. As
I have it set up now, I only get a report that gives me forecast dates 30
days plus and 30 days minus the current date. I'm using the following syntax:

Between Date( ) And DateAdd("M", 1, Date( )) OR
Between Date( ) And DateAdd("M", -1, Date( ))

I would use...

Year([ForecastDate]) = Year(Now( )) And Month ([ForecastDate]) = Month(Now( ))

...but this only gives me the current month, and I don't know how to work it
to give me next month and last month's dates.

Is there a way that I can get all of the current month, previous month and
following month in one report?

Between DateAdd("m",-1,Date()) and DateAdd("m",1,Date())

This will give 3 months to the day you run the query, i.e.
2-16-2005 thru 4-16-2005

If you want from the first of the previous month to the last of the
following month, regardless of the actual day the query is run, try:
Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+2,0)

2/1/2005 thru 4/30/2005

 

[fredg]

The string you gave me resulted in all march and april dates, but no february
dates. Can you see where the criteria may be missing the necessary pointer
for the previous month?

I would like a query that reports all forecast dates for the current month,
along with all forecast dates from the previous and following month. E.g.,
for the month of March, I want a report on February, March and April. I know
how to request the months specifically, but I would like something generic so
I don't have to change the dates in the query criteria field every month. As
I have it set up now, I only get a report that gives me forecast dates 30
days plus and 30 days minus the current date. I'm using the following syntax:

Between Date( ) And DateAdd("M", 1, Date( )) OR
Between Date( ) And DateAdd("M", -1, Date( ))

I would use...

Year([ForecastDate]) = Year(Now( )) And Month ([ForecastDate]) = Month(Now( ))

...but this only gives me the current month, and I don't know how to work it
to give me next month and last month's dates.

Is there a way that I can get all of the current month, previous month and
following month in one report?

Between DateAdd("m",-1,Date()) and DateAdd("m",1,Date())

This will give 3 months to the day you run the query, i.e.
2-16-2005 thru 4-16-2005

If you want from the first of the previous month to the last of the
following month, regardless of the actual day the query is run, try:
Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+2,0)

2/1/2005 thru 4/30/2005

My fault. Just subtract 1 from the month in the first function.

Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date())+2,0)