creating expressions to calculate multiple date differences on sam

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a list of 65,000 duplicate Client Identification Numbers
(CINs) out of an original list of 500,000. The duplicate CINs indicate that
a client has applied for enrollment to my program more than once.

I have a Date Of Birth (DOB) field, and an Application Date field for every
CIN. (FYI: most of the clients are under the age of 2 years).

Out of those 65,000 duplicate ID numbers, I'm wanting to determine the age
of the client EACH TIME they apply for enrollment, and I want to be able to
have a SEPARATE FIELD by age category, e.g., "< 1 month", "> or = 1 month but
< 3 months", "> or = 3 months but <5 months", etc.

What I've tried is this: In building an expression, I've said:

DateDiff ("d",[DOB],[Application Date]) and in the criteria area (still in
the Design View) indicated <31 (for less than 31 days).

The response I got from this indicated the 17,000 CINs that had responses
for this category, but I want to keep all 65,000 on the board for the next
field, which would be:

DateDiff ("m",[DOB],[Application Date]) and in the criteria area indicated
=1<3 (for greater than or equal to one month but less than 3 months).
Click to expand...

What happened is that I only get the response out of the 17,000 CINs, not
all 65,000, and I want it from all 65,000.

How do I build the expression to accomplish this? I think this may have to
do with Null/Not Null but I'm not sure how to do that.

Thank you!
 
I have created a list of 65,000 duplicate Client Identification Numbers
(CINs) out of an original list of 500,000. The duplicate CINs indicate that
a client has applied for enrollment to my program more than once.

I have a Date Of Birth (DOB) field, and an Application Date field for every
CIN. (FYI: most of the clients are under the age of 2 years).

Out of those 65,000 duplicate ID numbers, I'm wanting to determine the age
of the client EACH TIME they apply for enrollment, and I want to be able to
have a SEPARATE FIELD by age category, e.g., "< 1 month", "> or = 1 month but
< 3 months", "> or = 3 months but <5 months", etc.
Click to expand...

I'd suggest using the Switch() function; it takes arguments in pairs,
goes through the pairs left to right, and returns the second member of
the first pair for which the first member of the pair is TRUE. So:

Switch(DateDiff("d", [DOB], [Application Date]) < 31, "< 1 month",
DateDiff("d", [DOB], [Application Date]) < 90, "> or = 1 month but < 3
months",
<etc>,
True, "Date out of range")

Better might be to create a small table with the age ranges and their
corresponding names, and use DLookUp or a non-equi join to look up the
range name for each age:

AgeRanges
Low <Integer>
High <Integer>
RangeName <Text>

SELECT <whatever> FROM yourtable,
RangeName FROM yourtable INNER JOIN AgeRanges
ON DateDiff("d", [DOB], [Application Date]) >= AgeRanges.Low
AND DateDiff("d", [DOB], [Application Date]) < AgeRanges.High


John W. Vinson[MVP]
John W. Vinson[MVP]
 
Back
Top