Query Last Months Entries

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.

"James Hahn" wrote:

 > 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.
 > --
 > "FrankM" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
 > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
  > >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.
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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())


"FrankM" wrote:

 > 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.
 >
 > "James Hahn" wrote:
 >
  > > 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.
  > > --
  > > "FrankM" <FrankM RemoveThis @discussions.microsoft.com> wrote in message
  > > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   > > >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.
  > >
  > >
  > ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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?

"FrankM" wrote:

 > 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())
 >
 >
 > "FrankM" wrote:
 >
  > > 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.
  > >
  > > "James Hahn" wrote:
  > >
   > > > 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.
   > > > --
   > > > "FrankM" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
   > > > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   > > > >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.
   > > >
   > > >
   > > ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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())?
--
"FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
news:8DF0134F-196D-4552-9CA7-C0D5B3F307DA@microsoft.com...
 > 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())
 >
 >
 > "FrankM" wrote:
 >
  >> 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.
  >>
  >> "James Hahn" wrote:
  >>
   >> > 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.
   >> > --
   >> > "FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
   >> > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   >> > >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.
   >> >
   >> >
   >> ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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.




"James Hahn" wrote:

 > 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())?
 > --
 > "FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
 > news:8DF0134F-196D-4552-9CA7-C0D5B3F307DA@microsoft.com...
  > > 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())
  > >
  > >
  > > "FrankM" wrote:
  > >
   > >> 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.
   > >>
   > >> "James Hahn" wrote:
   > >>
   > >> > 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.
   > >> > --
   > >> > "FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
   > >> > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   > >> > >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.
   > >> >
   > >> >
   > >> >
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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" <FrankM DeleteThis @discussions.microsoft.com> wrote in message
news:DDCE29F0-48EA-42E5-971F-02278A7975F3@microsoft.com...
 > 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.
 >
 >
 >
 >
 > "James Hahn" wrote:
 >
  >> 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())?
  >> --
  >> "FrankM" <FrankM DeleteThis @discussions.microsoft.com> wrote in message
  >> news:8DF0134F-196D-4552-9CA7-C0D5B3F307DA@microsoft.com...
   >> > 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())
   >> >
   >> >
   >> > "FrankM" wrote:
   >> >
   >> >> 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.
   >> >>
   >> >> "James Hahn" wrote:
   >> >>
   >> >> > 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.
   >> >> > --
   >> >> > "FrankM" <FrankM DeleteThis @discussions.microsoft.com> wrote in message
   >> >> > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   >> >> > >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.
   >> >> >
   >> >> >
   >> >> >
  >>
  >>
  >><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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" wrote:

 > 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" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
 > news:DDCE29F0-48EA-42E5-971F-02278A7975F3@microsoft.com...
  > > 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.
  > >
  > >
  > >
  > >
  > > "James Hahn" wrote:
  > >
   > >> 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())?
   > >> --
   > >> "FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
   > >> news:8DF0134F-196D-4552-9CA7-C0D5B3F307DA@microsoft.com...
   > >> > 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())
   > >> >
   > >> >
   > >> > "FrankM" wrote:
   > >> >
   > >> >> 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.
   > >> >>
   > >> >> "James Hahn" wrote:
   > >> >>
   > >> >> > 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.
   > >> >> > --
   > >> >> > "FrankM" <FrankM.TakeThisOut@discussions.microsoft.com> wrote in message
   > >> >> > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   > >> >> > >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.
   > >> >> >
   > >> >> >
   > >> >> >
   > >>
   > >>
   > >>
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries 

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" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
news:148EC0FC-9AEA-478B-9284-E92F8EAC9787@microsoft.com...
 > 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" wrote:
 >
  >> 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" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
  >> news:DDCE29F0-48EA-42E5-971F-02278A7975F3@microsoft.com...
   >> > 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.
   >> >
   >> >
   >> >
   >> >
   >> > "James Hahn" wrote:
   >> >
   >> >> 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())?
   >> >> --
   >> >> "FrankM" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
   >> >> news:8DF0134F-196D-4552-9CA7-C0D5B3F307DA@microsoft.com...
   >> >> > 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())
   >> >> >
   >> >> >
   >> >> > "FrankM" wrote:
   >> >> >
   >> >> >> 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.
   >> >> >>
   >> >> >> "James Hahn" wrote:
   >> >> >>
   >> >> >> > 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.
   >> >> >> > --
   >> >> >> > "FrankM" <FrankM.DeleteThis@discussions.microsoft.com> wrote in message
   >> >> >> > news:3E050EF0-EA57-4A77-A3D8-2B28E30CEF51@microsoft.com...
   >> >> >> > >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.
   >> >> >> >
   >> >> >> >
   >> >> >> >
   >> >>
   >> >>
   >> >>
  >>
  >>
  >><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query Last Months Entries