Date function within a query

[Rob]

Hello,
I have a query that list individual projects, by project #, and the specific
date each was started. I need to add a criteria or field that will only
return projects that were started 180 days or 6 months ago. Is there a
function that will look at the current minus the start date > 180 days?
Below is the SQL:

SELECT [tbl Project Detail].[Project Number], [tbl Project
Detail].Description, [tbl Project Detail].[Current Month Spending], [tbl
Project Detail].[NVL Total CM Budget], [tbl Project Detail].[Total YTD
Spending], [tbl Project Detail].[Total TY Budget], [tbl Project
Detail].[Total Project Spending], [tbl Project Detail].[Total Project
Budget], [tbl Project Detail].[Remaining Budget Available (complete)], [tbl
Project Detail].[% of Budget Spent], [tbl Project Detail].CO, [tbl Project
Detail].BU, [tbl Project Detail].Dept, [tbl Project Detail].[Project Owner],
[tbl Project Detail].[Start Date], [tbl Project Detail].[End Date], [tbl
Project Detail].[Est Completion Date], [tbl Project Detail].Status, [tbl
Project Detail].Notes
FROM [tbl Project Detail]
WHERE ((([tbl Project Detail].[% of Budget Spent])<=0.25) AND (([tbl Project
Detail].Status)="Active"));

 

[SteveM]

DateAdd("d", -180, Date()) will return the day 180 days prior to the current
day.

Steve

 

[Steve Schapel]

Rob,

I am not sure whether you want to use 6 months, or 180 days. And it is
not clear from your description whether you mean projects started within
the last 6 months, or projects started more than 6 months ago.

Whatever the case, you can apply a criteria to the Start Date field in
your query.

If you want projects started more than 6 months ago, use this:
<DateAdd("m",-6,Date())

If you want projects started more than 180 days ago, use this:
<DateAdd("d",-180,Date())
.... or:
<Date()-180

If you want projects started within the last 6 months, use this:
<DateAdd("m",-6,Date())
.... although, if you have future projects already entered in the
database, and you don't want these included in the query's data set, use
this:
Between DateAdd("m",-6,Date()) And Date()

By the way, as an aside, it is not a good idea to use characters such as
# and ( and % as part of the name of fields.

 

[Rob]

Thank you very much. I was looking for projects started more than 6 months
ago, but thanks for all the answers.
Rob

Rob,

I am not sure whether you want to use 6 months, or 180 days. And it is
not clear from your description whether you mean projects started within
the last 6 months, or projects started more than 6 months ago.

Whatever the case, you can apply a criteria to the Start Date field in
your query.

If you want projects started more than 6 months ago, use this:
<DateAdd("m",-6,Date())

If you want projects started more than 180 days ago, use this:
<DateAdd("d",-180,Date())
.... or:
<Date()-180

If you want projects started within the last 6 months, use this:
<DateAdd("m",-6,Date())
.... although, if you have future projects already entered in the
database, and you don't want these included in the query's data set, use
this:
Between DateAdd("m",-6,Date()) And Date()

By the way, as an aside, it is not a good idea to use characters such as
# and ( and % as part of the name of fields.

--
Steve Schapel, Microsoft Access MVP

Hello,
I have a query that list individual projects, by project #, and the specific
date each was started. I need to add a criteria or field that will only
return projects that were started 180 days or 6 months ago. Is there a
function that will look at the current minus the start date > 180 days?
Below is the SQL:

SELECT [tbl Project Detail].[Project Number], [tbl Project
Detail].Description, [tbl Project Detail].[Current Month Spending], [tbl
Project Detail].[NVL Total CM Budget], [tbl Project Detail].[Total YTD
Spending], [tbl Project Detail].[Total TY Budget], [tbl Project
Detail].[Total Project Spending], [tbl Project Detail].[Total Project
Budget], [tbl Project Detail].[Remaining Budget Available (complete)], [tbl
Project Detail].[% of Budget Spent], [tbl Project Detail].CO, [tbl Project
Detail].BU, [tbl Project Detail].Dept, [tbl Project Detail].[Project Owner],
[tbl Project Detail].[Start Date], [tbl Project Detail].[End Date], [tbl
Project Detail].[Est Completion Date], [tbl Project Detail].Status, [tbl
Project Detail].Notes
FROM [tbl Project Detail]
WHERE ((([tbl Project Detail].[% of Budget Spent])<=0.25) AND (([tbl Project
Detail].Status)="Active"));