CODEWITHSUNDEEP
sqlms-access
Soham
Soham

Reputation: 873

Querying for records on a date with an offset from a given date

Consider I have a date as 29/03/2011. I want to query records for one day, 5 day and 10 day post this date.

Say, the sample fields are F1,F2,F3;

My code goes like this

SELECT F1,F2,F3 FROM T1 WHERE T1.Date = ...

I am using MS Access 2007, and I am not able to understand how to query for a date which is having an offset.

I have tinkered and experimented with DateAdd() but is giving me an error. Would like to have a solution.

Soham

Upvotes: 1

Views: 822

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

In Access, you can use:

SELECT F1,F2,F3 
FROM T1 
WHERE T1.Date >= #2011-07-11# + 10

To show all records which are 10 days and afterwards a certain date.

or this to show all records with are exactly 10 days from a certain date:

SELECT F1,F2,F3 
FROM T1 
WHERE T1.Date = #2011-07-11# + 10

There is a huge problem though, if your dates have a time part! The above will not catch records where date = #2011-07-11 11:43#. It will match only records having time part 00:00:00. So it's equivalent to having:

SELECT F1,F2,F3 
FROM T1 
WHERE T1.Date = #2011-07-21 00:00:00#

Which is probably not what you want. It's preferable to use this:

SELECT F1,F2,F3 
FROM T1 
WHERE DateValue(T1.Date) = #2011-07-11# + 10

or this, which can use an index on the Date field, so it's the best approach:

SELECT F1,F2,F3 
FROM T1 
WHERE T1.Date >= #2011-07-11# + 10
  AND T1.Date <  #2011-07-11# + 11

Upvotes: 1

Related Questions