>90 days

[lilbit27]

I have a fill called billefft with dates that can range from 1/1/05
through 5/01/07

and I have a field called rpt date that is always 1/31/07 or the last
date of the month each time the file is downloaded at month end.

I am trying to write a query that for the criteria for the billeff
date is say give me everything that is >90 days from the rptdate which
is 1/31/07. So dates that are from 11/1/06 through 11/30/06 would not
be picked up because they are considered 60 day items. I need
everything that is 10/31/06 and prior.

--------------------------------------------------------------------------------

 

[Arvin Meyer [MVP]]

If it's a fixed date, the criteria would be:

<#1/31/07# - 90

If it's a date field, the criteria would read:

<[DateField] - 90

 

[lilbit27]

If it's a fixed date, the criteria would be:

<#1/31/07# - 90

If it's a date field, the criteria would read:

<[DateField] - 90
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I have a fill called billefft with dates that can range from 1/1/05
through 5/01/07

and I have a field called rpt date that is always 1/31/07 or the last
date of the month each time the file is downloaded at month end.

I am trying to write a query that for the criteria for the billeff
date is say give me everything that is >90 days from the rptdate which
is 1/31/07. So dates that are from 11/1/06 through 11/30/06 would not
be picked up because they are considered 60 day items. I need
everything that is 10/31/06 and prior.

---------------------------------------------------------------------------­------ Hide quoted text -

- Show quoted text -

That still pulls in 11/1-11/30 day items and they are not >90 days old
they are 60 days.

 

[Ron2006]

Are all of the fields DEFINED as date fields and not text fields?

Because they look like date fields does not mean that the data really
is being stored as a date field and that makes a significant
difference in how the query has to be written.

Ron

 

[Arvin Meyer [MVP]]

Oops, that's because it ought to be:

[DateField]-90

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

If it's a fixed date, the criteria would be:

<#1/31/07# - 90

If it's a date field, the criteria would read:

<[DateField] - 90
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I have a fill called billefft with dates that can range from 1/1/05
through 5/01/07

and I have a field called rpt date that is always 1/31/07 or the last
date of the month each time the file is downloaded at month end.

I am trying to write a query that for the criteria for the billeff
date is say give me everything that is >90 days from the rptdate which
is 1/31/07. So dates that are from 11/1/06 through 11/30/06 would not
be picked up because they are considered 60 day items. I need
everything that is 10/31/06 and prior.

---------------------------------------------------------------------------­------
Hide quoted text -

- Show quoted text -

That still pulls in 11/1-11/30 day items and they are not >90 days old
they are 60 days.

 

[Ron2006]

Oops, that's because it ought to be:

[DateField]-90

--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

If it's a fixed date, the criteria would be:

<#1/31/07# - 90

If it's a date field, the criteria would read:

<[DateField] - 90

- Show quoted text -

That still pulls in 11/1-11/30 day items and they are not >90 days old
they are 60 days.- Hide quoted text -

- Show quoted text -

Arvin, I think your original test is correct.

greater than a date that is 90 days ago gets those that are within the
last 90 days not those that were before then.

If the field is being stored as a text field then 1/ is I believe
less than 10/.

Ron

 

[Arvin Meyer [MVP]]

Arvin, I think your original test is correct.

See what happens when I jump to conclusions? You're correct of course. My
first answer was correct. It is:

<[DateField]-90