Last year - Year to date total

[dwood@atx]

Hello everyone,

I have a query that pulls current year, month, and Quarter to date totals. I would like to compare each to the same time frame from the previous year.

So if today is August 16, I want a comparative for everything up to August 16th last year.

The formula I came up with works for some records but for others the total ends up being way bigger than the total of everything in the database.

this is the formula I was using

LYValue: Sum(IIf([PODate]<=DateSerial(Year(Date())-1,Month(Date()),Day(Date())),[USValue],0))

Please let me know if I need any further explination, this is my first time posting. I have always been able to search and find an answer on here...not so this time.

Thanks so much!

 

[Ranman256]

i use a form that has 4 text boxes: txtStartDate ,txtEndDate and txtPrevYrStart ,txtPrevYrEnd
tho users cant see prev year boxes.

User sets the start /end date via radio buttons: monthly , yearly, YTD, quarter, etc.
the vb sets the start/end dates for both sets of text boxes
txtPrevYrStart = DateAdd("yyyy",-1,txtStartDate)
txtPrevYrEnd = DateAdd("yyyy",-1,txtEndDate)

then the query just pulls from the form
select * from table where [date] between forms!frmMain!txtStartDate and forms!frmMain!txtEndDate

 

[Ranman256]

date reporting from form via radio buttons

 

[dwood@atx]

i use a form that has 4 text boxes: txtStartDate ,txtEndDate and txtPrevYrStart ,txtPrevYrEnd
tho users cant see prev year boxes.

User sets the start /end date via radio buttons: monthly , yearly, YTD, quarter, etc.
the vb sets the start/end dates for both sets of text boxes
txtPrevYrStart = DateAdd("yyyy",-1,txtStartDate)
txtPrevYrEnd = DateAdd("yyyy",-1,txtEndDate)

then the query just pulls from the form
select * from table where [date] between forms!frmMain!txtStartDate and forms!frmMain!txtEndDate

Thanks so much this is a great idea. It actually turned out that my problem was in my data import from excel. I assumed my issue was the formula I was using in my query but when I went back to the table I saw my numbers were off. I re imported my data and its all good now.

Denise