Help with DateDiff in query

[emik]

Hi everyone, I've been really stuck on this so I hope someone can help me out.

I have an access 2007 database and am trying to do a calculation on the dates.

I am taking the average over 5 years, so from my transactions table I take the date field and I drop the year and group (to get the average for 01/01). I then want to add in the current year, so I take those first 5 characters and add the current year:

forecast_date: DateValue(Left([Value_Date],Len([Value_Date])-5) & "/" & Year(Date()))

The problem is I want the query to return only dates that are greater than today and less than today + 35. I tried using DateDiff:

datedifftest: DateDiff("d",[forecast_date],Date()) and it returns a number but I am not able to put a criteria on it. I even tried creating a sub query where my criteria was >0 and <35 but I get "Data type mismatch in criteria expression.

Any feedback is much appreciated.

 

[emik]

Still doesn't work, but I'm close!

Either I'm able to get now + 35 with the correct averages (but no overlap to 2012) or I get the 2012 overlap with the wrong averages.

The solution I thought I had actually starts 35 days in the future. So the averages obviously don't add up.

DateAdd is definately the way to go, just haven't figured out the right field or criteria to get it to give me today + 35 into the future (with the year changing if day now + 35 is 2012).

 

[boblarson]

Why doesn't it work? The date add for what I just gave you works fine for me as far as returning the date we need in the future.

 

[emik]

Maybe I'm not explaining what I need to do or maybe this is a more complicated problem and I need to write some VBA.

What you gave me gives me the correct date of today + 35 but what I need is today with the next 35 days:

April 6 $$
April 7 $$
April 8 $$
....etc

If the date is Dec 29 I get:
Dec 29, 2011
Dec 30, 2011
Dec 31, 2011
Jan 01, 2011 <--- I need this year to change to 2012

So how do I take that daily average, append the current year to the first date and do a DateAdd for the next 34 days that would flow over into 2012?

Could this possibly be a 2 query solution?

 

[Kreszch68]

I'm still convinced that you better create a result table.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The code I gave you takes about 1 millisecond to create this table for you and all your problems are solved.<o></o>

 

[emik]

Ya I think I might go that route, that way I can any code off the table. I'll play around with it today and let you know how it goes.

Thanks!

 

[emik]

I finally figured out a solution to this mess.

With my ActualDate table (which is my souce table) I have the query take the average for every day (excluding the year). In my Excel file I have my vlookups based on the month/day only. In a hidden row I have the date calculated including the year which I will then re-upload into a Historic_Forecast table in Access via SQL.

Thank you everyone for all your feedback and help!