Plus or Minus....

[nodge87]

I am trying to show up lines in a table with matching serial numbers and also term dates which cover roughly, the same period.

i.e. 2 Lines in a table with the same serial numbers and then the dates as follows. Line 1: 1-1-09 to 12-31-09
AND Line 2: 1-3-09 to 12-31-09.

Both lines cover pretty much the same period. But is there a way of querying for "Date1" = "Date2" PLUS OR MINUS A FEW DAYS?????

Really stuck on this one!! Thanks!

[SDas]

nodge87, I am not sure of the exact Access syntax but you don't want to use =. Either BETWEEN or >= <= would work better. For example

WHERE DATE1 BETWEEN DATEADD(D,-2,DATE2) AND DATEADD(D,2,DATE2)
or
WHERE DATE1 >= DATEADD(D,-2,DATE2) AND DATE1 <= DATEADD(D,2,DATE2)

I think the DATEADD function is the correct one. It is adding either -2 or 2 Days to DATE2.

I used 2 for a + or - range but you can use whatever you need.

[nodge87]

Hi SDas,

I tried to use the DATEADD functiono but it keeps asking me to enter "d" for the datepart???

I do not know what to type in and whenever I type anything in there it brings back an error???

Thanks!!

[SDas]

nodge87, DATEADD has as its parameters

The date unit you want to affect. D = Day, M = Month, etc (see the manual for the complete list).

The amount to be added.

The Date value.

So, DATEADD(D, 2, date-field) should add 2 Days to the value of the Date-Field.

If you are getting an Error, try a lowercase d instead of D. I don't know if it makes a difference or not.

[GolferGuy]

Nodge87, the D that is in the Interval parameter needs to a a string. Right now, the D that is in the function's parameters is being looked at as a variable. So, just write the function like this:
DATEADD("d", 2, date-field)
By making that D into an actual string ("d") the function will then work.