How to calculate 15 days from today's date based off of a date field in access table
Hello Experts,
I am trying to identify rows where the date field in that row is 15 days out from today's date. I want to create another column that tells me whether or not the date is 15 days or less away. Please see my date example below
DATE
06/01/2011
11/01/2010
10/01/2010
10/17/2010
I am trying to identify rows where the date field in that row is 15 days out from today's date. I want to create another column that tells me whether or not the date is 15 days or less away. Please see my date example below
DATE
06/01/2011
11/01/2010
10/01/2010
10/17/2010
Patrick Matthews
SELECT TheDate, IIf(TheDate <= Date() + 15, "Yes", "No") AS Within15DaysFROM SomeTable
Define a column as:
DaysAway: DateDiff("d",Now(), <DateFieldName>)
and then put a criteria check on it of <16
JimD.
DaysAway: DateDiff("d",Now(), <DateFieldName>)
and then put a criteria check on it of <16
JimD.
use a combination of dateadd and datediff to accomplish this, could you give more info on how you are doing this? Since you have this in zones for Query Syntax and Access Coding/Macros I wasn't sure how you were doing this. Is it a Query or a Module?
ASKER
I am doing this in a query.
ASKER
I tried
DATE 15 DAYS OUT: IIf(DateDiff("d",Now(),[TA
When I try this without the <16 criteria, it calculates the days accurately. However, when I added the criteria, it doesn't calculate accurately. Thoughts?
DATE 15 DAYS OUT: IIf(DateDiff("d",Now(),[TA
When I try this without the <16 criteria, it calculates the days accurately. However, when I added the criteria, it doesn't calculate accurately. Thoughts?
Please post your SQL statement.
Your example shows three dates, nothing else.
ASKER
SELECT IIF(DateDiff("d",Now(),[TA
INTO NEWTABLE
FROM TABLENAME;
INTO NEWTABLE
FROM TABLENAME;
I am not sure why it is not working for you, I just wanted to be sure that your fields are date fields, you are using Now() function which returns a datetime, Date() function just returns a date...
If your field data includes time values then datediff when comparing against another time (especially one that changes, as Now() does) you might get unexpected results especially if you are displaying your dates in a field formatted/masked to only show date format.
I have run:
IIf(DateDiff("d", Date, DateAdd("d", 16, Date)) < 16, "yes <16", "no >=16"))
result: no >=16
IIf(DateDiff("d", Date, DateAdd("d", 15, Date)) < 16, "yes <16", "no >=16"))
result: yes <= 15
IIf(DateDiff("d", Date, DateAdd("d", 1, Date)) < 16, "yes <16", "no >=16"))
result: yes <= 15
IIf(DateDiff("d", Date, DateAdd("d", 1, Date)) < 16, "yes <16", "no >=16"))
result: no >= 16
If your field data includes time values then datediff when comparing against another time (especially one that changes, as Now() does) you might get unexpected results especially if you are displaying your dates in a field formatted/masked to only show date format.
I have run:
IIf(DateDiff("d", Date, DateAdd("d", 16, Date)) < 16, "yes <16", "no >=16"))
result: no >=16
IIf(DateDiff("d", Date, DateAdd("d", 15, Date)) < 16, "yes <16", "no >=16"))
result: yes <= 15
IIf(DateDiff("d", Date, DateAdd("d", 1, Date)) < 16, "yes <16", "no >=16"))
result: yes <= 15
IIf(DateDiff("d", Date, DateAdd("d", 1, Date)) < 16, "yes <16", "no >=16"))
result: no >= 16
sorry that last one should be:
IIf(DateDiff("d", Date, DateAdd("d", 40, Date)) < 16, "yes <16", "no >=16"))
IIf(DateDiff("d", Date, DateAdd("d", 40, Date)) < 16, "yes <16", "no >=16"))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all of your help. It works fine now!!!