4 months less from today in where

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

How to set up a restrictions as to show the month, which
is 4 months less from today.

I'm trying
Rec: Format([Received],"m/yyyy") in the field
and Format(Date(),"m/yyyy")-4 in criteria.
But, it says type mismatch in criteria.

Thanks
 
Date()-120

Or use the datepart to evaluate what month this is and look at the datepart
of the month you are evaluating and see if it is four months ago.

This is a hard question since we don't know exactly what you want here.
February only has 28 days, so four months before March 1 (March 1 - 120
days) would be November 1. March 31 minus 120 days would be December 1.

So, what is it you are looking for?

Rick B



How to set up a restrictions as to show the month, which
is 4 months less from today.

I'm trying
Rec: Format([Received],"m/yyyy") in the field
and Format(Date(),"m/yyyy")-4 in criteria.
But, it says type mismatch in criteria.

Thanks
 
I have date column - [Recieved] and I need to compare 4
previous years and 4 previous months.
To show only rows for the 4 years before current year and
so on, I'm using Format(Date(),"yyyy")-4 in the Criteria
and to have fiscal year (started from August) I'm using
Rec: Year(DateAdd("m",+5,[Received])) in the Field. It
looks like working.

But, I don't know how to show rows for the month, which
is -4 month from current and so on.

Thanks
 
I tried:
EDATE(Format(Date(),"m/yyyy"),-4)
But, it says undefined function in expression despite I
have Msowcf.dll.
-----Original Message-----
I have date column - [Recieved] and I need to compare 4
previous years and 4 previous months.
To show only rows for the 4 years before current year and
so on, I'm using Format(Date(),"yyyy")-4 in the Criteria
and to have fiscal year (started from August) I'm using
Rec: Year(DateAdd("m",+5,[Received])) in the Field. It
looks like working.

But, I don't know how to show rows for the month, which
is -4 month from current and so on.

Thanks
-----Original Message-----
Date()-120

Or use the datepart to evaluate what month this is and look at the datepart
of the month you are evaluating and see if it is four months ago.

This is a hard question since we don't know exactly what you want here.
February only has 28 days, so four months before March 1 (March 1 - 120
days) would be November 1. March 31 minus 120 days
Click to expand...
would
be December 1.
So, what is it you are looking for?

Rick B



How to set up a restrictions as to show the month, which
is 4 months less from today.

I'm trying
Rec: Format([Received],"m/yyyy") in the field
and Format(Date(),"m/yyyy")-4 in criteria.
But, it says type mismatch in criteria.

Thanks


.
Click to expand...
.
Click to expand...
 
It looks like I've done it.

I've added a column for the year with Format$(Date
(),"yyyy") in Criteria and a column for the month with
Format$(DateAdd("m",-4,Date()),"mm").
-----Original Message-----
I tried:
EDATE(Format(Date(),"m/yyyy"),-4)
But, it says undefined function in expression despite I
have Msowcf.dll.
-----Original Message-----
I have date column - [Recieved] and I need to compare 4
previous years and 4 previous months.
To show only rows for the 4 years before current year and
so on, I'm using Format(Date(),"yyyy")-4 in the Criteria
and to have fiscal year (started from August) I'm using
Rec: Year(DateAdd("m",+5,[Received])) in the Field. It
looks like working.

But, I don't know how to show rows for the month, which
is -4 month from current and so on.

Thanks
-----Original Message-----
Date()-120

Or use the datepart to evaluate what month this is and look at the datepart
of the month you are evaluating and see if it is four months ago.

This is a hard question since we don't know exactly
Click to expand...
what
you want here.
February only has 28 days, so four months before March 1 (March 1 - 120
days) would be November 1. March 31 minus 120 days
Click to expand...
would
be December 1.
So, what is it you are looking for?

Rick B



How to set up a restrictions as to show the month, which
is 4 months less from today.

I'm trying
Rec: Format([Received],"m/yyyy") in the field
and Format(Date(),"m/yyyy")-4 in criteria.
But, it says type mismatch in criteria.

Thanks


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi,



DateInThePast >= DateAdd("m", -4, Date())

subtracts 4 months to the third argument, here, the actual date, then
compare that result to another date in the past. Today, it will be to keep
records with:


DateInThePast >= #21-02-2004#


On the other hand, if you want any date in March, April, May and June
(because today, we are in June, so, any dates in the four past month):

DateInThePast >= DateSerial(Year(Date()), Month( Date() ) - 3 , 1)


would be preferable. Again, for today, that is equivalent to:

DateInThePast >= #3-1-2004#




Sure, there can be almost a full month of difference between both
expression, mainly if you are at the beginning of a new month.




Hoping it may help,
Vanderghast, Access MVP
 
How to set up a restrictions as to show the month, which
is 4 months less from today.

I'm trying
Rec: Format([Received],"m/yyyy") in the field
and Format(Date(),"m/yyyy")-4 in criteria.
But, it says type mismatch in criteria.

Thanks
Click to expand...

You're getting a type mismatch because you're trying to subtract the
number 4 from the text string 7/2004. This will actually give you a
value (-3.996506986027944), but apply it as a criterion to a text
string. Try a criterion on the datefield of
= DateSerial(Year(Date()), Month(Date()) - 4, 1) AND < DateSerial(Year(Date()), Month(Date()) - 3, 1)
Click to expand...

The DateSerial function will return a date/time value, and these two
will give the range of dates from March 1, 2004 through the end of
March 31, 2004 if run today.
 
Back
Top