Last 6 months but not last years data

[gregatvrm]

I am trying to get just the last 6 months of data from todays date for the
current year. SQL below works if your in the 7th month but, when I set my
system clock to say March to test it, it gives me previous years data. I
need to do this for last 30 days, last 6 months and year to date. Can I use
a quarterly function or is there a simpler method?

SELECT Sum(Table1.Field1) AS SumOfField1, Sum(Table1.Field2) AS SumOfField2,
Sum(Table1.Field3) AS SumOfField3, Sum(Table1.Field4) AS SumOfField4
FROM [Table1]

Thanks for the help!
WHERE (((Table1.SalesDate) Between Now()-180 And Now() Or
(Table1.SalesDate)>#1/1/2008#));

 

[gregatvrm]

Complete SQL as written:

SELECT Sum(Table1.Field1) AS SumOfField1, Sum(Table1.Field2) AS SumOfField2,
Sum(Table1.Field3) AS SumOfField3, Sum(Table1.Field4) AS SumOfField4
FROM [Table1]
WHERE (((Table1.SalesDate) Between Now()-180 And Now() Or
(Table1.SalesDate)>#1/1/2008#));

 

[John Spencer]

SELECT Sum(Table1.Field1) AS SumOfField1
, Sum(Table1.Field2) AS SumOfField2
, Sum(Table1.Field3) AS SumOfField3
, Sum(Table1.Field4) AS SumOfField4
FROM [Table1]
WHERE Table1.SalesDate Between DateAdd("m",-6,Date()) And Date()
AND
Table1.SalesDate >= DateSerial(Year(Date()),1,1)

In other words change the OR to AND



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[gregatvrm]

Works Perfectly!!!!!!!

Thanks John.

SELECT Sum(Table1.Field1) AS SumOfField1
, Sum(Table1.Field2) AS SumOfField2
, Sum(Table1.Field3) AS SumOfField3
, Sum(Table1.Field4) AS SumOfField4
FROM [Table1]
WHERE Table1.SalesDate Between DateAdd("m",-6,Date()) And Date()
AND
Table1.SalesDate >= DateSerial(Year(Date()),1,1)

In other words change the OR to AND



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Complete SQL as written:

SELECT Sum(Table1.Field1) AS SumOfField1, Sum(Table1.Field2) AS SumOfField2,
Sum(Table1.Field3) AS SumOfField3, Sum(Table1.Field4) AS SumOfField4
FROM [Table1]
WHERE (((Table1.SalesDate) Between Now()-180 And Now() Or
(Table1.SalesDate)>#1/1/2008#));