hullo. I have a table in msaccess 2003 with budget and actuals(seperated by Bud for budget and act for actual). The table has 4 fields: Date,Account,Amout,Seperator(Bud/Act).the data runs from 2008 to date. I want a query that will show [a][U]current month(actual-Budget-Variance);[B]same month prevous year(actual-Budget-Variance); [C]3months to date(actual-Budget-Variance) and 3months same period prevous year(actual-Budget-Variance);

What is the question?

I want an sql statement that will pick the (1). same month prevous year(Actual); and 3months to date(Actual)and 3months same period prevous year(actual);

First of all, do not use "Date" as a field name. "Date" is an Access key word.

Use Access help and "date functions" to build the requisite date criteria.

E.g., if dtDate is the subject date,

same Date previous year = DateAdd("yyyy",1,dtDate)

1st day of current month = Cdate(Month(dtDate) & "/1/" & year(dtDate))

I'll be that you need date intervals, not just a specific date.

i use Between Date() And DateAdd("M",-3,Date()) and its okay, but i want the same(3 months to date ) for the prevous year.

Between DateAdd("yyyy",-1,Date()) And DateAdd("yyyy",-1,DateAdd("M",-3,Date()))

Access Help gives a good explaination of the DateAdd function.

It has not given me what i want

Why not give some workable examples of your needs, i.e I have February 03, 2007. I need February 03, 2006, and March 03, 2007.

Something like that would help us help you. The examples you throw out don't even have to be real ones, just so you can get the syntax down.

__________________
Shane

If your issue is resolved...mark it so - here's how!

Assuming we are in April 2010. When i use Between Date() And DateAdd("M",-3,Date()) i get data for 3months to date ie feb to april 2010. I want to get data for the same period last year. the table has Date and amount and data for 2009 and 2010 up to april(2010).

How about wrapping that whole thing, well, the two date boundaries anyway, with another DateAdd function to remove a year?

Between DateAdd("Y",-1,Date()) and DateAdd("Y",-1,DateAdd("M",-3,Date()))

will this not give you the range you need?

__________________
Shane

If your issue is resolved...mark it so - here's how!

It did not. Any alternatives?