AZGroups.com

Hi,

 I am fairly new to Microsoft Access and I needed some help trying to filter some dates.  I need to want to view only the training that has been completed within the last 6 months. So, take 6 months and go forward and that's what I want to see. I have a column in my db named date and that is what I want filtered. I don't care if you give me the SQL answer or the expression builder in access. I am using Microsoft Access 2000-2003. I've tried a few things but I'm not sure if I am even close. Thanks for the help.

-George 

Since you are using Access, I would use the DateAdd function and add a negative six months from now.  There isn't a subtract date function but add handles negative numbers as well as positive.   The exrpession builder would be:

>=DateAdd("m",-6,Now())

The sample SQL would look like the following.  Be sure to replace the * with the actual fields that you need.  It is good coding practice to avoid the select * and only bring back what you need.  By specifying the actual fields you gain performance and extra help find bugs with the program.  For example lets say the date field is changed to TrainingDate.  The error message will state "date" is not a valid column name.  You now just have to look at the query and see the name change.  I also like to use aliases on the columns so the rest of my code won't change when the column names change. 

SELECT *
FROM Table1
WHERE [date]>=DateAdd("m",-6,Now()); 

If you wish to have the records of the last six months based off the first of the current month then use this next expression.  It takes todays month and year and sets the day as the 1st.

 >=DateAdd("m",-6,DateSerial(Year(Now()),Month(Now()),1))

Have fun coding,

--J Man