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
-- J Man
Super Hero Esquire III
jaminman@gmail.com