Query Last Months Entries

F

FrankM

I have a table which will contain 90 days worth of entries and after 90 days
I want the older entries to be appended to a new table. I need two Queries
one that will Append the old entries based on the date field and the
parameter of 90 days (which I think I have) and then a second Query which
will display last months entries (this is the one I'm having problems with).
I'm using "<(Now()-90)" for the 90 days Append but I can't seem to do
anything with the last month Query. Using 30 days won't work because I don't
know if the Query will be used the first of the month, the 15th or the 30th,
regardless it needs to display the last months entries.
 
J

James Hahn

I assume that 'last month' means the month before the current month.

Use the dateadd function with Now() and the month parameter 'm' and numeric
value -1 to get a date in the previous month. Use the month() function with
that date to get the month number for the previous month. Use the month()
function with the date from the data records to get the month number for
each record, and compare the two to identify records from last month.

Note that this result will be inconsistent with the -90 you are using for
the other task. You really need to rewrite the archiving procedure to also
use month numbers rather than number of days.
 
F

FrankM

Are you recommending using something like "DateAdd("m",-1,Now())"?
Unfortunately that did not work. Maybe I've got something wrong in there. I'm
going to give it another shot in the morning. I've got a headache right now.

I realize that the previous month and the 90 days seems inconsistent and
believe me we've had that conversation and to be honest I agree with you. But
for this purpose they are being treated separately and one does need to be by
days whereas the other needs to be the previous month. I know, I know.
 
F

FrankM

OK, I tried these two and neither accomplished what I was hoping. Am I
missing something really simple?

DateAdd("m",-1,Now())
DateAdd("m",-1,Date())
 
F

FrankM

OK, I found another part of my Query seems to be interfering. The Critera
"DateAdd("m",-1,Now())" seems to work but if I have the Critera to include
"Good" in another field it doesn't seem to work. When I took out "Good" from
another field it worked but I want to include "Good" in another field. The
other field is a Status field and is either "Pending" or is "Good". I want
all the "Good" entries from the previous month. Does this need to be
separated into two different Queries?
 
J

James Hahn

I just tried it and DateAdd("m",-1,Now()) gave me 18th of January and
Month(DateAdd("m", -1, Now())) gave me '1', which is the month number of the
previous month.

What result did you get for DateAdd("m",-1,Now())?
--
 
F

FrankM

The Table has about 250 Entries dated between January 1, 2005 and January 31,
2005.

DateAdd("m",-1,Now()) ... No Responses.
DateAdd("m",-1,Date()) ... No Responses.
Month(DateAdd("m",-1,Now())) ... No Responses.
Month(DateAdd("m",-1,Date())) ... No Responses.

And even more confusing, I thought this worked when I took out the other
Criteria. But it isn't working and right now this is the ONLY Criteria in the
Query. Ideally, I wanted all the Entries with the Status "Good" in the
Previous month. I thought having the other Criteria, Status="Good" might be
causing the issue but now I don't think so. I'm getting more confused. Thanks
for your assistance.
 
J

James Hahn

If you are doing this in a query, you need a calculated field for

Month([DateField])

This gives you the month number of the field ([DateField] or whatever it's
called) that you are testing. Then you need a criteria for this calculated
field of

Month(DateAdd("m",-1,Now()))

which gives you the month number of the month before the current month. So
that when the month number of last month matches the month number of the
date in the record, there is a match.
--
 
F

FrankM

Thank you so much for your response. Another person here told me I could also
use the DatePart function rather than the DateAdd.

In my Query I added a new field ...

DatePart("m",[Complete Date])

.... and then for the Criteria I entered the recommendation they made ...

DatePart("m",Date())-1

.... everything seems to work great. Obviously the original field is
"Complete Date". But this seemed to do the trick. Thanks for all your
assistance.

There's always more than one way to accomplish anything.



James Hahn said:
If you are doing this in a query, you need a calculated field for

Month([DateField])

This gives you the month number of the field ([DateField] or whatever it's
called) that you are testing. Then you need a criteria for this calculated
field of

Month(DateAdd("m",-1,Now()))

which gives you the month number of the month before the current month. So
that when the month number of last month matches the month number of the
date in the record, there is a match.
--
FrankM said:
The Table has about 250 Entries dated between January 1, 2005 and January
31,
2005.

DateAdd("m",-1,Now()) ... No Responses.
DateAdd("m",-1,Date()) ... No Responses.
Month(DateAdd("m",-1,Now())) ... No Responses.
Month(DateAdd("m",-1,Date())) ... No Responses.

And even more confusing, I thought this worked when I took out the other
Criteria. But it isn't working and right now this is the ONLY Criteria in
the
Query. Ideally, I wanted all the Entries with the Status "Good" in the
Previous month. I thought having the other Criteria, Status="Good" might
be
causing the issue but now I don't think so. I'm getting more confused.
Thanks
for your assistance.
Click to expand...
Click to expand...
 
J

James Hahn

There might be several ways to accomplish it, but not all of them work.

DatePart("m",[Complete Date]) for a date in December will return 12 as the
month number for that date.

DatePart("m",Date())-1 for a date in January will return 0 as the month
number of the previous month.

There won't be a match and the items won't be extracted properly. You would
be advised to stick with the original suggestion.
--
FrankM said:
Thank you so much for your response. Another person here told me I could
also
use the DatePart function rather than the DateAdd.

In my Query I added a new field ...

DatePart("m",[Complete Date])

... and then for the Criteria I entered the recommendation they made ...

DatePart("m",Date())-1

... everything seems to work great. Obviously the original field is
"Complete Date". But this seemed to do the trick. Thanks for all your
assistance.

There's always more than one way to accomplish anything.



James Hahn said:
If you are doing this in a query, you need a calculated field for

Month([DateField])

This gives you the month number of the field ([DateField] or whatever
it's
called) that you are testing. Then you need a criteria for this
calculated
field of

Month(DateAdd("m",-1,Now()))

which gives you the month number of the month before the current month.
So
that when the month number of last month matches the month number of the
date in the record, there is a match.
--
FrankM said:
The Table has about 250 Entries dated between January 1, 2005 and
January
31,
2005.

DateAdd("m",-1,Now()) ... No Responses.
DateAdd("m",-1,Date()) ... No Responses.
Month(DateAdd("m",-1,Now())) ... No Responses.
Month(DateAdd("m",-1,Date())) ... No Responses.

And even more confusing, I thought this worked when I took out the
other
Criteria. But it isn't working and right now this is the ONLY Criteria
in
the
Query. Ideally, I wanted all the Entries with the Status "Good" in the
Previous month. I thought having the other Criteria, Status="Good"
might
be
causing the issue but now I don't think so. I'm getting more confused.
Thanks
for your assistance.




:

I just tried it and DateAdd("m",-1,Now()) gave me 18th of January and
Month(DateAdd("m", -1, Now())) gave me '1', which is the month number
of
the
previous month.

What result did you get for DateAdd("m",-1,Now())?
--
OK, I tried these two and neither accomplished what I was hoping. Am
I
missing something really simple?

DateAdd("m",-1,Now())
DateAdd("m",-1,Date())


:

Are you recommending using something like "DateAdd("m",-1,Now())"?
Unfortunately that did not work. Maybe I've got something wrong in
there.
I'm
going to give it another shot in the morning. I've got a headache
right
now.

I realize that the previous month and the 90 days seems
inconsistent
and
believe me we've had that conversation and to be honest I agree
with
you.
But
for this purpose they are being treated separately and one does
need
to
be by
days whereas the other needs to be the previous month. I know, I
know.

:

I assume that 'last month' means the month before the current
month.

Use the dateadd function with Now() and the month parameter 'm'
and
numeric
value -1 to get a date in the previous month. Use the month()
function
with
that date to get the month number for the previous month. Use the
month()
function with the date from the data records to get the month
number
for
each record, and compare the two to identify records from last
month.

Note that this result will be inconsistent with the -90 you are
using
for
the other task. You really need to rewrite the archiving
procedure
to
also
use month numbers rather than number of days.
--
I have a table which will contain 90 days worth of entries and
after
90
days
I want the older entries to be appended to a new table. I need
two
Queries
one that will Append the old entries based on the date field
and
the
parameter of 90 days (which I think I have) and then a second
Query
which
will display last months entries (this is the one I'm having
problems
with).
I'm using "<(Now()-90)" for the 90 days Append but I can't seem
to
do
anything with the last month Query. Using 30 days won't work
because
I
don't
know if the Query will be used the first of the month, the 15th
or
the
30th,
regardless it needs to display the last months entries.
Click to expand...
Click to expand...
Click to expand...
 
Top