I need to only display records where datediff("d",date1,date2)>10
datediff is not "web compatible". What would be a workaround?
A:
In Access, you can do date arithmetic directly with Date/Time values. So this expression will return 1.5:
Debug.Print #2001/01/02 12:00 PM# - #2001/01/01 00:00 AM#
If you're wanting a difference in whole days, see if the Int() function is available. In Access, this one returns 1:
Debug.Print Int(#2001/01/02 12:00 PM#) - Int(#2001/01/01 00:00 AM#)
Sorry I don't know what is web compatible.
HansUp
2010-06-25 19:10:23
Me neither, hence the comment.
Remou
2010-06-25 19:28:40
I was confused by "default in Access for subtracting dates is days". I wanted to clarify the difference (in Access) can include fractional days.
HansUp
2010-06-25 19:39:20
Web compatible means a query built in Access 2010 that going to run on the web server. So this refers to the new web publishing feature in Access 2010. While web queries have a decent function set, they are missing some of the long time VBA functions we come to expect in the Access client queries. You also can't switch to sql view when building web query (you are restricted to query grid). So, web building is new for 2010, and thus the original question since no datediff() is available.
Albert D. Kallal
2010-06-26 14:13:32
A:
For a web query, you don't have dateDiff() function, but as the others point out, a simple difference between the two values will give you the number of days.
So in the query builder just type in
DAYDIF: ([date2]-[date1])
And, in the criteria, you can go:
> 10
Albert D. Kallal
2010-06-26 12:31:14
How do you do date/time calculations in web queries other than days? And what about the problem if any of the values have time parts (which could change the results)?
David-W-Fenton
2010-06-26 22:15:21
Well, you do have the Hour() min(), functions. You also have DateSerial(Year,Month,Day), and you also have Year(), month(), day() functions. So, between this set of functions you not in too bad of shape.
Albert D. Kallal
2010-06-27 02:44:37
But those don't really replace DateDiff(), because they don't have built-in calendar knowledge. E.g., adding 1 month with DateSerial() and Month() won't necessarily get you the same result as DateDiff().
David-W-Fenton
2010-06-28 21:48:15
It will give you same result if you use date serial. so, both will give you the same result: DateAdd("m", 1, MyDt) or DateSerial(Year(MyDt), Month(MyDt) + 1, Day(MyDt))
Albert D. Kallal
2010-06-29 06:27:08
But DateAdd("m",1,#1/31/2009#) and DateSerial(Year(#1/31/2009#),Month(#1/31/2009#)+1, Day(#1/31/2009#)) are not the same dates. That's kind of my point.
David-W-Fenton
2010-06-29 22:34:53
Ah, I see. This depends on how you want to define months. In your example you already on the last day of the month. You can use the long time trick of DateSerial(Year(dt), Month(dt) + 2, 0) and that will always jump you to the end of the next month less 1 (including when leap year occurs). In my experience if one is not at the end or start of the month, then I can't say I ever used dateadd() with values from the "middle" of a month and added 1 to that value. Above trick does let one bracket month ranges with correct month ends but there is still a loss of functionally as you point out.
Albert D. Kallal
2010-06-30 02:54:06
It's not me defining months -- it's part of the built-in date handling. Decomposing a date into its component numbers, doing an arithmetic operation on them and then reassembling with DateSerial() does not take advantage of the calendar tables behind DateAdd() and DateDiff(). Whether or not you need it is an open question, but the point is that without DateAdd(), you have to use much more complex expressions.
David-W-Fenton
2010-07-01 02:01:30
As mentioned I hard pressed to think of applications where I needed this ability. So yes we do loose some functionality here but for the most part it not a big loss. I write nothing but reservation systems that deal with dates. I can't recall where I need to add one month to the 30 th and there is 31 days in the next month but I still want the 30 th as a result. In my cases I wanted the LAST month day. So DateSerial with day = 0 DOES give me that end of month date base on the calendar. If these easy workarounds don't apply then this issue will be more of loss. but for me it been not a problem.
Albert D. Kallal
2010-07-01 04:11:23
Thus you wouldn't be using DateDiff() in the first place. I rely on DateDiff() (and DateAdd()) because it knows about the calendar and produces results I can rely on. Rolling my own calculations with DateSerial() is not something I look forward to in web-only forms.
David-W-Fenton
2010-07-02 00:27:05