Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / July 2006

Tip: Looking for answers? Try searching our database.

creating expressions to calculate multiple date differences on sam

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Celinda - 26 Jul 2006 19:56 GMT
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).

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!
John Vinson - 26 Jul 2006 21:33 GMT
>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
[quoted text clipped - 7 lines]
>have a SEPARATE FIELD by age category, e.g., "< 1 month", "> or = 1 month but
>< 3 months", "> or = 3 months but <5 months", etc.

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]
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.