Matching up Dates

[sstump]

OK...hopefully I can explain this clearly.

I have two tables, we'll use Table 1 and Table 2. Each table has a date field, Date 1 and Date 2. Since I'm not experienced with Code, I would prefer to do this in an Expression.

I need to find all records in [Table 2]![Date 2] field that comes within 5 days before or 5 days after the date in [Table 1]![Date 1].

I tried to build the following expression in the criteria under [Table 2]![Date 2].

Between DateAdd("d", -5, [Table 1]![Date 1]) AND DateAdd("d", 5, [Table 1]![Date 1].

However that doesn't reveal any records, and I know there are some.

Any suggestions??

Thanks,
Shawn Stump

 

[BuGlen]

Are the tables joined in the query? Post the SQL from the query and it may be easier to find the issue.

Glen Appleton

VB.Net student.

 

[sstump]

I got it!

What I did was run the original query with just the dates and made two additional fields that calculated the date difference on Date 1 (plus and minus 5 days). Then set that to a Make Table Query. Then built a second query off of the new table and entered "Between [Expr1] and [Expr2]" in the Criteria. It might be the long way around it, but it seems to work great.

Thanks!

 

[dneufarth]

Between DateAdd("d",[table1].[date 1],-5) And DateAdd("d",[table1].[date 1],5)

should do it


Dave

 

[sstump]

That's what I tried the first time and it didn't like it. I'll wrestle with it later and see what I can come up with. Got about 18 other things to get done today also.

Thanks!

 

[dneufarth]

check your syntax; and placement of arguments in DateAdd

don't know if it makes a difference, but my query against some test tables worked just as you wanted.

Dave

 

[JonFer]

You can also do this:

Where Abs(Date1-Date2) <= 5

 

[sstump]

I do have one last question on this subject. How do I get the opposite. To not show me anything between these time periods?

 

[Golom]

Just negate the condition as in

   Where

NOT (

Abs(Date1-Date2) <= 5

)

 

[sstump]

Would I also need a >= 5 to get the other side. Again I need 5 days befer the date and 5 days after.

 

[Golom]

You could use &quot;> 5&quot; to get the opposite as well. Note that &quot;>= 5&quot; and &quot;<= 5&quot; will both return &quot;= 5&quot; so they are not exactly opposite.

You can use

   Where NOT (Abs(Date1-Date2) <= 5)

OR

   Where Abs(Date1-Date2) > 5

Either should give you the &quot;more than 5 days before or after&quot; records. The &quot;Abs&quot; function takes before of the &quot;before&quot; and &quot;after&quot; stuff so you don't need separate conditions to test for them.