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.

 

[xenou]

Hi,
Welcome to the board.

It's not clear what your expression returns to you since we can't see the data or the result of the expression:
forecast_date: DateValue(Left([Value_Date],Len([Value_Date])-5) & "/" & Year(Date()))
Is the above getting a date with year/month/day (15-Jan-2011) in it? Or is it always day 1 of the month?

However, for your filter criteria you need a where clause in your query to get a certain range of dates:
WHERE {Date} > Date() AND {Date} < (Date() + 35)

Be sure to check whether this includes the 1st or 35th day correctly.
Date() is of course, an Access function that returns the current system date (without a time).

 

[emik]

Hi xenou,

Thanks for your reply. The data in the table (the field I care about is this):

value_date (dd/mm/yyyy):
01/01/2005
01/01/2006
...etc

So what I'm trying to do is to get all the averages for January 1 (regardless of the year). To do this, the first part of my string returns the following:

"01/01"

Now all my Jan 1 are grouped together (getting me the average amount) but to put it into my report I need to add the year, so the final string returns:

"01/01/2011"

Then the datediff is on this new value "01/01/2011" to compare to today's date. The solution you gave me doesn't seem to be working as I'm getting the same error. Even though I'm getting a value for my datedifftest field:

datedifftest: DateDiff("d",[forecast_date],Date())

-51
11
...etc

but any numerical validations don't work, so I'm not sure what type this value is being returned as. I tried doing a val(datedifftest) but no luck. I'm going to play around with the WHERE clause to see if I can get it. Even a simple > 35 as a test didn't work. I hope I provided enough information.

Thanks.

 

[Phildaburn]

Using this data as a basis and your formula for changing an old date into a date this year, I came up with this:
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Date of Birth</TH><TH bgColor=#c0c0c0 borderColor=#000000>forecast_date</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1/15/1960</TD><TD borderColor=#d0d7e5 align=right>1/15/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2/18/1961</TD><TD borderColor=#d0d7e5 align=right>2/18/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/25/1962</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4/8/1963</TD><TD borderColor=#d0d7e5 align=right>4/8/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5/1/1964</TD><TD borderColor=#d0d7e5 align=right>5/1/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6/30/1965</TD><TD borderColor=#d0d7e5 align=right>6/30/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>12/18/2010</TD><TD borderColor=#d0d7e5 align=right>12/18/2011</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

By putting "Between Date() And Date()+35" in the criteria section of my query, I end up with this:
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Date of Birth</TH><TH bgColor=#c0c0c0 borderColor=#000000>forecast_date</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/25/1962</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4/8/1963</TD><TD borderColor=#d0d7e5 align=right>4/8/2011</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Is this what you were wanting to obtain?

Phil...

 

[emik]

Hi Phil,

Yes that is what I'm trying to do but it didn't work for me. Here is what my query looks like:

<TABLE style="WIDTH: 303pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=403><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=20 width=68>Daily_Key</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 62pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=83>avg_amount</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=78>group_date</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 70pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=93>forecast_date</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 61pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=81>datedifftest</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=68 align=right>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=83>2082</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=78>01/02</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl71 width=93>01/01/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=81>52</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=68 align=right>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=83>1976</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=78>01/03</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 width=93>01/03/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=81>24</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=68 align=right>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=83>2550</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=78>01/04</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 width=93>01/04/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=81>-7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=68 align=right>4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=83>2196</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=78>01/05</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 width=93>01/05/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl70 width=81>-37</TD></TR></TBODY></TABLE>

daily_key = expression
avg_amount = expression
group_date = group by
forecast_date = expression
datedifftest = expression

avg_amount: Round(Avg([Amount]),0)
group_date: Left([Value_Date],Len([Value_Date])-5)
forecast_date: DateValue(Left([Value_Date],Len([Value_Date])-5) & "/" & Year(Now()))
datedifftest: DateDiff("d",[forecast_date],Date())

I have a feeling the answer is something simple that I'm just forgetting to do or a type conversion required somewhere.

Thanks.

 

[Phildaburn]

None of the dates in your [forecast_date] field results are within the date range of today and today + 35.

 

[emik]

Sorry that was just a small sample. The full results are 5 years of data for every day of the year. So I need the average of all Jan 1, Jan 2...etc

Also my data is stored as dd/mm/yyyy

 

[Phildaburn]

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

I would change "Now()" to "Date()". The Now() function includes time values.
In the query design view, just below the check box (on the first row of the criteria line) that you see below your "forecast_date" field, put in the "Between Date() and Date()+35" expression (no quotations).

Phil...

 

[emik]

I get "Data type mismatch in criteria expression."

I removed all the fields and kept just the 2:

group_date
forecast_date (I made the change now to date)

Total: Expression

Criteria in the forecast_date field: Between Date() and Date()+35

 

[Phildaburn]

If you remove the criteria expression and run the query, do the dates line up on the right side of the cell or the left side of the cell? If they're on the left, you're getting text that looks like a date value rather than an actual date value.

Let me know how it turns out.

Phil...