Query question

[Earniep]

I'm a new Access user. I have a table which shows the number of days between
two date fields. What I am trying to do is create a query that will count
the records that fall within a certain range (i.e., +/- 14 days).

Sample fields in my table are:

MRP date 1 MRP date 2 Change
01/01/2009 01/06/2009 5
02/01/2009 02/16/2009 15
02/01/2009 02/15/2009 14
03/05/2009 03/28/2009 23

I would greately appreciate any help anyone can provide.

 

[tina]

try

SELECT Count(Change)
FROM MyTable
WHERE Change Between -14 And 14;

replace MyTable with the correct table name, of course.

hth

 

[Larry Linson]

tina's answer is correct for the question you asked, but I have a question
for you: instead of storing the calculated difference (field "Change",
number of days) in the table, why do you not calculate it in the Query that
you use to access the data?

Information which can be calculated when needed from other fields in the
Record is redundant and not good relational database design practice. The
DateDiff function, well-documented in the module window's VBA help file, can
be used in the Query to calculate it, and appropriate criteria (as suggested
by tina) applied to the calculated field.

And, you will avoid the "headscratching moment" that often occurs when you
store calculated values that, later, when you examine a record and find that
the "Change" field does not actually represent the difference between the
two dates. <Don't laugh: It happens all too often when calculated fields
are stored, and some later change does not cover all the cases where they
should be recalculated.>

Larry Linson
Microsoft Office Access MVP