Criteria: between the 1 and last day of 2 months ago

[Guest]

Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two months ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's date was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[Edward G]

here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?

 

[Guest]

You're genious. It works.
Thanks.

here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?

Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two months ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's date was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[tina]

PMFJI, but if you're wanting to *always* go back two calendar months from
the current month, then

Format(Date()-60,"mm/yy")

will not do that for you consistently. when you happen to run the query on
the last day of any month, it will return the previous month rather than 2
months back; in August, the last 2 days of the month will give you July. if
you happen to run the query on March 1, it will kick you back to December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth

here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?

Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two

months

ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's date was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[Edward G]

good point!
thanks!

PMFJI, but if you're wanting to *always* go back two calendar months from
the current month, then

Format(Date()-60,"mm/yy")

will not do that for you consistently. when you happen to run the query on
the last day of any month, it will return the previous month rather than 2
months back; in August, the last 2 days of the month will give you July. if
you happen to run the query on March 1, it will kick you back to December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth

here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?

Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two

months

ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's

date
was

the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[tina]

you're welcome, and thank you, too! i doubt that i would have thought to use
the Format() function to ensure getting the correct *year* and month in the
query - i would probably have come up with some convoluted mess to handle
that issue. your solution made it so neat and easy. i usually need a good
nudge like that to think outside the "box" of my previous experience!

good point!
thanks!

PMFJI, but if you're wanting to *always* go back two calendar months from
the current month, then

Format(Date()-60,"mm/yy")

will not do that for you consistently. when you happen to run the query on
the last day of any month, it will return the previous month rather than 2
months back; in August, the last 2 days of the month will give you July. if
you happen to run the query on March 1, it will kick you back to December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth

here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?
Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two months
ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's date
was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[John Spencer (MVP)]

Solution is good, but for really large data sets I would use something like the
following - even though it is not as simple to understand.

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-2,1) And DateSerial(Year(Date()),Month(Date())-1,0)

This would be more efficient as it would not need to run the format function on
every record AND if the date field was indexed this version can use the index to
more rapidly identify the records to retrieve.

you're welcome, and thank you, too! i doubt that i would have thought to use
the Format() function to ensure getting the correct *year* and month in the
query - i would probably have come up with some convoluted mess to handle
that issue. your solution made it so neat and easy. i usually need a good
nudge like that to think outside the "box" of my previous experience!

good point!
thanks!

PMFJI, but if you're wanting to *always* go back two calendar months from
the current month, then

Format(Date()-60,"mm/yy")

will not do that for you consistently. when you happen to run the query on
the last day of any month, it will return the previous month rather than 2
months back; in August, the last 2 days of the month will give you July. if
you happen to run the query on March 1, it will kick you back to December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth


here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?
Hi,
I have a query with a date field. I want the criteria to return all the
records with the date beween the first day and the last day of two
months
ago.
Example: today's date is 12/10/2004, I want my query to return all the
records whose date is between 10/1/2004 and 10/31/2004. If today's date
was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a solution.

Thank you for your help.

Alec

 

[tina]

ah, more efficient is always better - i love adding a new tool to my box.
thx, John!

Solution is good, but for really large data sets I would use something like the
following - even though it is not as simple to understand.

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-2,1) And DateSerial(Year(Date()),Month(Date())-1,0)

This would be more efficient as it would not need to run the format function on
every record AND if the date field was indexed this version can use the index to
more rapidly identify the records to retrieve.

you're welcome, and thank you, too! i doubt that i would have thought to use
the Format() function to ensure getting the correct *year* and month in the
query - i would probably have come up with some convoluted mess to handle
that issue. your solution made it so neat and easy. i usually need a good
nudge like that to think outside the "box" of my previous experience!

good point!
thanks!


PMFJI, but if you're wanting to *always* go back two calendar months from
the current month, then

Format(Date()-60,"mm/yy")

will not do that for you consistently. when you happen to run the

query
on

the last day of any month, it will return the previous month rather

than
2

months back; in August, the last 2 days of the month will give you July.
if
you happen to run the query on March 1, it will kick you back to December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth


here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?
Hi,
I have a query with a date field. I want the criteria to return all
the
records with the date beween the first day and the last day of two
months
ago.
Example: today's date is 12/10/2004, I want my query to return

all
the

records whose date is between 10/1/2004 and 10/31/2004. If today's
date
was
the 1/25/2005, the date should be between 11/1/2004 and 11/30/2004.

I tried to mess around with DateAdd but I cant seem to find a
solution.

Thank you for your help.

Alec

 

[Edward G]

"i love adding a new tool to my box."

Amen! And thank you for your kind words.

E

ah, more efficient is always better - i love adding a new tool to my box.
thx, John!

Solution is good, but for really large data sets I would use something like the
following - even though it is not as simple to understand.

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-2,1) And DateSerial(Year(Date()),Month(Date())-1,0)

This would be more efficient as it would not need to run the format function on
every record AND if the date field was indexed this version can use the index to
more rapidly identify the records to retrieve.

to

use

in

the

rather

than

2
months back; in August, the last 2 days of the month will give you July.
if
you happen to run the query on March 1, it will kick you back to
December
instead of January - except during leap year.

for a consistent return value, try

Format(DateAdd("m", -2, Date()), "mm/yy")

in the criteria of your calculated query field called Date1.

hth


here, try this:
Create a new field in your query like this:
Date1: Format([YourDateField],"mm/yy")
and in the criteria enter this:
Format(Date()-60,"mm/yy")

Does that work?
Hi,
I have a query with a date field. I want the criteria to

return

all