Eternally Functional Criteria

[Guest]

How do I change the following criteria so that I don't have to go in after
January 1 every year and change the year? The table on which the query is
based continues to contain loans from January of the previous year for awhile
after Jan. 1 of the current year, so my query must make sure that after Jan.
1 2007 (for example) it always excludes loans from January through November
of 2006.

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=2006)

Thanks so much!
GwenH

Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

 

[Ken Snell \(MVP\)]

Perhaps this, although I am not clear about what months/year to use for each
"month" of a year:

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=Year(Now()))

I'm not sure if you want to include December 2006 when the Now() value is a
date/time within the month of January 2007; if you do, this won't work as
stated. But tell us more specifics about which selection criteria apply in
which months.

 

[Guest]

The query in question is based on a table called "loanActivities", which
contains a list of all new and renewed loans for the past 18 months. In
January 2006 I need the query to pull loans for December 2005. In February
2006, I need it to pull loans for January 2006. In January 2007, I need it to
pull loans for December 2006.

The problem with the criteria I was using was that in January 2007, it would
pull loans for December 2005 *and* December 2006, when I only needed December
2006. So, I added the "AND

((Year([Date_of_Loan))=2006)" portion.

Does that help?

Perhaps this, although I am not clear about what months/year to use for each
"month" of a year:

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=Year(Now()))

I'm not sure if you want to include December 2006 when the Now() value is a
date/time within the month of January 2007; if you do, this won't work as
stated. But tell us more specifics about which selection criteria apply in
which months.
--

Ken Snell
<MS ACCESS MVP>

How do I change the following criteria so that I don't have to go in after
January 1 every year and change the year? The table on which the query is
based continues to contain loans from January of the previous year for
awhile
after Jan. 1 of the current year, so my query must make sure that after
Jan.
1 2007 (for example) it always excludes loans from January through
November
of 2006.

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=2006)

Thanks so much!
GwenH

Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

 

[raskew via AccessMonster.com]

Hi -

The following (change field names as appropriate) will return all records
from the month prior to when it is run.

SELECT
tblInput.InputID
, tblInput.InputDate
, tblInput.InputText
FROM
tblInput
WHERE
(((tblInput.InputDate) between DateSerial(Year(Date()),Month(Date())-1,1)
AND
DateSerial(Year(Date()),Month(Date()),0)))
ORDER BY
tblInput.InputDate;

HTH - Bob

Perhaps this, although I am not clear about what months/year to use for each
"month" of a year:

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=Year(Now()))

I'm not sure if you want to include December 2006 when the Now() value is a
date/time within the month of January 2007; if you do, this won't work as
stated. But tell us more specifics about which selection criteria apply in
which months.

How do I change the following criteria so that I don't have to go in after
January 1 every year and change the year? The table on which the query is

[quoted text clipped - 13 lines]

Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

 

[Guest]

Thank you! That was exactly what the query needed.

Hi -

The following (change field names as appropriate) will return all records
from the month prior to when it is run.

SELECT
tblInput.InputID
, tblInput.InputDate
, tblInput.InputText
FROM
tblInput
WHERE
(((tblInput.InputDate) between DateSerial(Year(Date()),Month(Date())-1,1)
AND
DateSerial(Year(Date()),Month(Date()),0)))
ORDER BY
tblInput.InputDate;

HTH - Bob

Perhaps this, although I am not clear about what months/year to use for each
"month" of a year:

((Month([Date_of_Loan]))=Format(DateAdd("m",-1,Now()),"m"))) AND
((Year([Date_of_Loan))=Year(Now()))

I'm not sure if you want to include December 2006 when the Now() value is a
date/time within the month of January 2007; if you do, this won't work as
stated. But tell us more specifics about which selection criteria apply in
which months.

How do I change the following criteria so that I don't have to go in after
January 1 every year and change the year? The table on which the query is

[quoted text clipped - 13 lines]

Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor