If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
90 days
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. -------------------------------------------------------------------------------- |
#2
|
|||
|
|||
90 days
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, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "lilbit27" wrote in message oups.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. -------------------------------------------------------------------------------- |
#3
|
|||
|
|||
90 days
On Feb 5, 9:06 am, "Arvin Meyer [MVP]" wrote:
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 "lilbit27" wrote in message oups.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. |
#4
|
|||
|
|||
90 days
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 |
#5
|
|||
|
|||
90 days
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 "lilbit27" wrote in message oups.com... On Feb 5, 9:06 am, "Arvin Meyer [MVP]" wrote: 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 "lilbit27" wrote in message oups.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. |
#6
|
|||
|
|||
90 days
On Feb 5, 5:41 pm, "Arvin Meyer [MVP]" wrote:
Oops, that's because it ought to be: [DateField]-90 -- Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com "lilbit27" wrote in message oups.com... On Feb 5, 9:06 am, "Arvin Meyer [MVP]" wrote: 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.accessmv... "lilbit27" wrote in message roups.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.- 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 |
#7
|
|||
|
|||
90 days
"Ron2006" wrote in message ups.com... 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 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
Thread Tools | |
Display Modes | |
|
|