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.

 

[Kreszch68]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
First I have to say that normally I’d prefer using functions etc., so this is not how I would do it myself. But to help you out I’ll give you the solution for what you’re after. The query will always return the average amount starting with the upcoming day and the next 34 days (so 35 days in total).
AvgAmount:
the average amount (ok, this is obvious)
AvgOverNYears:
the number of years the average amount is based on
ForeCastDate:
the date that was giving you a headache

DataAvg: to be replaced with your table name
DateField: to be replaced with your date field

[FONT=Verdana][FONT=Verdana]SELECT Avg(CDbl([Amount])) AS [B]AvgAmount[/B], Count(DataAvg.Amount) AS [B]AvgOverNYears[/B], CDate(Day([DateField]) & "-" & Month([DateField])) AS [B]ForeCastDate[/B][/FONT]
[FONT=Verdana]FROM [B]DataAvg[/B][/FONT]
[FONT=Verdana]GROUP BY Day([DateField]), Month([DateField]), CDate(Day([DateField]) & "-" & Month([DateField]))[/FONT]
[FONT=Verdana]HAVING (((CDate(Day([DateField]) & "-" & Month([DateField]))) Between Now() And Now()+35))[/FONT]
[FONT=Verdana]ORDER BY CDate(Day([DateField]) & "-" & Month([DateField]))[/FONT]
[/FONT]

Note: Maybe you need to replace the "-" with a "\"

 

[emik]

Hi Kreszch68,

It works! I'm breaking down the SQL now to understand what's actually happening. The only thing I can't quite figure out is (obviously) the ForeCastDate...

I see you're taking the month and day, but how is the current year being appended to it? CDate is converting it to a date, I guess that's what adds the year?

You said you prefer doing this through functions, how would you do in this case?

I have an Excel spreadsheet that is linked to the query to populate the contents of a report (vlookups...etc).

Thank you everyone for your considerable amount of help and being patient with me Who knows how long I would have been stuck on this.

As this is my first post on this message board, is there anything I need to do now that it's working? Changing the topic to SOLUTION: ...etc?

 

[Kreszch68]

Emik,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
the current year is appended by the system indeed.<o></o>
If you want to add year by passing a string, you'll get an error as soon as you set a where statement on this field. Why?<o></o>
Well, imagine what happens when you want to retrieve records that match a criteria. All records have to be compared to the criteria, which in this case is a calculated value. And at a point in time there is a day <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:metricconverter w:st="on" ProductID="29 in">29 in</st1:metricconverter> month 2. If you stitch this combination to a year that’s not a leaping year, like 2011, the conversion to a valid date will fail, raising the error that has been driving you nuts. By not entering the year this problem is taken care of by the compiler. <o></o>
That’s why you shouldn’t use concatenation to create a date value (when using it to set a where clause). For now you’re saved, but if you want do add a year other than the current year, you’ll find yourself in really big trouble (unless it’s a leaping year). <o></o>

 

[emik]

Then I might be in trouble, as I mentioned I forecast 35 days into the future, after that 13 weeks (by week) and 8 months (by month). So when the current date is Dec 01, 2011, I will need to forecast all of Dec 2011 and part of January 2012 (as it's a rolling total).

What can I do to prevent the error happening when the year switches, ie forecast year <> current year?

 

[Kreszch68]

Delete all feb/29/xxxx entries from the data.
There can't be more than one or two.

 

[emik]

2012 is a leap year, so I will need to have a forecast for that day. I need a way to get around it and not simply omit it. I can play around with it from this point and try to find a solution.

Thanks again.

 

[xenou]

There may be better solutions but I frequently just manipulate dates with DateSerial(), Year(), Month(), and Day(). These four functions can get you quite a lot.

I.e., for the year following the forecast year:

Year(ForecastYear) + 1

 

[Kreszch68]

Well, there is an other way.
You'll need a function to test if a year is a leaping year.
If that's the case, you can add the 29th in the collection for that year (being the current year) if not, skip omit the 29th.

You can use the mod operator to test if year can be devided by 4, 100 and 400. If mod returns 0, you have a leaping year.

BUT, don't use a function in a query to create a criteria field on the fly. This will take day's to evaluate, writting the data by hand is faster.
Instead use an array or something else that works in memory, and do your calculations there. I know this is not a really big help now, let me explain later (have to leave for now).

 

[revans]

Function IsLeapYear(TestYear) As Boolean
  IsLeapYear = IsDate("2/29/" & TestYear)
End Function

hth,

Rich

 

[emik]

Quick update, spoke with the user about forecasting for the 29th (with 5 years of data there will be only 1 transaction from 4 years ago) so it was decided to group any data for Feb 29 with the Feb 28 data.