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]

The group date is left aligned (01/01) and the forecast_date is right aligned (01/01/2011).

I did the same test as you and created a sample table and the Between Date() and Date()+35 works. I just can't get it to work on my existing query where I build the date.

Thanks

 

[xenou]

I can't follow what you are after. You can't both pull only Jan 1 dates, and pull dates that are within 35 days of today. There is no Jan 1 date within 35 days of today.

Can you provide a better set of sample data and expected results.

 

[emik]

Hi xenou,

Here is an example from March for the previous 3 years:

<TABLE style="WIDTH: 210pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=280><COLGROUP><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 108pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=144>Value_Date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 102pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=136>Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 align=right>28/03/2008</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 align=right>28/03/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 align=right>28/03/2010</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>300</TD></TR></TBODY></TABLE>

So:
$100 for March 28, 2008
$200 for March 28, 2009
$300 for March 28, 2010

Now I need to forecast the data for March 28, 2011. What I need to do is take the average for all previous March 28 to get my forecast amount. To do this I drop the year which leaves me with:

28/03
28/03
28/03

Now I can group up the date and average the amount.

This should create 1 record with the average of the 3 years:

$200 28/03

Now I need to say which year (I'm forecasting multiple years in the future) so I need to append 2011 to it, the end result I'm looking for is:

$200 28/03/2011

In my forecast report I need only today and 35 days in the future. So instead of bringing all this data my Excel report I want to filter it to only give me the data set I need. ie March 25 - April 29

I hope this clears it up a little. Thank you.

 

[Phildaburn]

I had a thought on the commute home from work about this expression:

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

forecast_date: datevalue(month([value_date]) & "/" & day([value_date]) & "/" & year(date()))
Does this alternative formula work?

Phil...

 

[xenou]

Okay, that's clear.
But I'm foreseeing problems with this.

For instance, Mar 28 2011 is a Monday. But Mar 28 2010 is a Sunday. So it's not a good "apples to apples" comparison. Also, we'll have troubles with leap years - these days will not exist in all years.

Maybe better would be to subtract 52 weeks, 104 weeks, and 156 weeks ... that would be an exact comparison to the same day 52 weeks ago, or 104 weeks ago, or 156 weeks ago.

What do you think?
For Mar 28, for instance, the result is:
3/28/2011 - today
3/29/2010 - one year ago (monday)
3/30/2009 - two years ago (monday)
3/31/2008 - three years ago (monday)

ξ

 

[Phildaburn]

To do what you're describing now will involve you changing how you're grouping the data using the Datepart function. To get the week-of-the-year number, use this: "Week: DatePart("ww",[value_date])" then to get the day of the week, use this: "DayOfWeek: DatePart("w",[value_date])"
The second expression will return numbers 1 through 7 where 1 = Sunday, 2 = Monday, etc.

So, grouping by Week and DayOfWeek will match up your Monday's of the same week of each year regardless of calendar date. From that, you'll need to generate your averages.

In a separate query called ReportRange (for example), do this:

SELECT Date() AS Today, DatePart("ww",Date()) AS TodayWeek, DatePart("w",Date()) AS TodayDoW, DatePart("ww",Date()+35) AS FutureWeek, DatePart("w",Date()+35) AS FutureDoW;

Add this query like you would a table to your query. In the criteria for Week put this: "Between [TodayWeek] and [FutureWeek]" and for the criteria for DayOfWeek you can simply put [TodayDoW] since both the beginning and ending dates in your range are on the same day of the week. If you change the range from 35 to something else, I would use "Between [TodayDoW] and [FutureDoW]". You would also need to change the +35 in the ReportRange query to something else as well.

Hope this makes sense. Let me know if it helps.

Phil...

P.S. Obviously I've not tested this. I'm having 2nd thoughts about the 2nd query. You may have to utilize the DatePart expressions from ReportRange as the criteria in your original query.

 

[xenou]

I don't think (week of year) + (day of week) will work. For instance, Friday of week one in 2010 is Jan 1. But there is no Friday of week one in 2009 (Null). In other words, there's no precise match of weeks counted by week numbers, since years don't have even numbers of weeks in them (365 / 7 leaves a remainder).

 

[Phildaburn]

What Xenou is saying is correct. The Week of Year calculation can return 53.

It seems like what you're doing, emik, is creating a sales forecasting tool. If it were mine, I'd want to know what my sales were for a specific date over time rather than the first monday of each year. So, in order to get your query working, let's go back to it for a moment.

The results you obtained from your test field have bothered me - they don't look like they should. So, for the sake of this conversation, let's call your query as it exists now qryCalculations. From it, remove the Between Date() and Date()+35 criteria.

I'd like you to make a new query - qrySelect - using qryCalculations as the datasource. In qrySelect, use the Between Date() and Date()+35 criteria. I'd also like to see you repeat your datetest calculation to see the results.

Let me know how this turns out, please.

Phil...

 

[emik]

Hi guys,

I wasn't in the office last week but I will take a look at it today and let you know how it goes!

 

[emik]

I still get the same error when I put in the "between" criteria for the data type mismatch.

Just to give you some more details, I am forecasting 35 days into the future, then 13 weeks (by week) after that and then 8 months after that (by month).

xenou you brought up a good point about days falling on weekends (or leap years) and I have already spoken to the user when I started developing this application. We have 5 years of previous data so in the best case scenerio she would get 5 years of data and worst case she would get 3 (excluding the weekend). For holidays they are not shown in this spreadsheet so that shouldn't have any impact.

The way I approached this application was to create my "Actuals" table where I have fields for the week, month, date of the year. I then have 3 queries:

-daily (the part I'm stuck on, by day)
-weekly (groups up all the data by the specific week of the year)
-monthly (groups up all the data by the specific month of the year)

Could I be looking at this from the wrong perspective? Any suggestions on how I could get the daily average another way?

Thank you for taking the time to reply to this post.