var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: How do I write in a date range?
I'd like to write a function that automatically runs a 12-month rolling report (rather than supplying a specific start and end date). In Crystal, I would write this as:
Claims.CurrentDateTime in CURRENTDATE-1 to CURRENTDATE-366
Any idea how to do this in Access? Thanks!
Comparable in Access would be
Claims.CurrentDateTime Between Date()-1 And Date()-366
Assuming that's the exact business logic you want, then pbaldy is on point.
However, I would recommend using the DateDiff() and DateAdd() functions for evaluating datetime fields as these functions will automatically account for oddities such as leap years.
um, guys, i hate to break it to you but the earlier date has to go first
this is due to the fact that BETWEEN is actually executed as a pair of inequalities
WHERE foo BETWEEN DATE()-365 AND DATE()-1
the same logic applies to numbers, so for instance if you write this --
WHERE foo >= DATE()-365
AND foo <= DATE()-1
this would be equivalent to
WHERE foo BETWEEN 8 AND 2
and now it's perhaps a little easier to see why this will
WHERE foo >= 8
AND foo <= 2
never return any rows
I would agree and always structure my own statements that way, but I was curious, having seen it work the other way. I tested Access queries against SQL Server linked tables and a local Access table. The queries against the linked tables behaved exactly as you describe, but queries against the local table didn't care. I tested both numeric and date fields. In either case, it didn't matter which value was first, they would return the correct values either way. Does Jet evaluate/execute the SQL differently based on the table?
well whaddya know, i just tested it and i guess it does
Originally Posted by
still, putting the higher BETWEEN value ahead of the lower one is a bad habit to get into, because i can say with certainty that it does ~not~ work in other databases
Last edited by r937; 12-23-09 at
I agree with you 100%, I was just curious.