Dr.K
07-26-2008, 08:03 AM
Ok, so I have a table that tracks 401K contribution rates. Its set up as a 'profile' table; If you pull all the records for a specific participant, each row has:
-Data Field (the percentage contribution)
-Source Code
-Effective Date
-Expiration Date
If the record is currently active, the Expiration date is Null.
Ok, what I need, is a list of all relevant Source Codes, during a specific time period (between datFirst and datLast). The rest of the SQL is complicated, but it works, so here is the relevant part of the WHERE clause:
& " AND ( " _
& " ( TABLE.EXPIRATION_DATE <= 'datLast'" _
& " AND TABLE.EFFECTIVE_DATE >= 'datFirst' )" _
& " OR" _
& " ( ( TABLE.EXPIRATION_DATE >= 'datLast'" _
& " OR TABLE.EXPIRATION_DATE IS NULL ) " _
& " AND TABLE.EFFECTIVE_DATE < 'datLast' )" _
& " )" _
Query edited for clarity. Yes, I know that you can't put a variable directly into a literal date string, I just removed the text formatting to make it easier to read)
Does this capture EVERY code that was valid between datFirst and datLast, or am I missing something?
-Data Field (the percentage contribution)
-Source Code
-Effective Date
-Expiration Date
If the record is currently active, the Expiration date is Null.
Ok, what I need, is a list of all relevant Source Codes, during a specific time period (between datFirst and datLast). The rest of the SQL is complicated, but it works, so here is the relevant part of the WHERE clause:
& " AND ( " _
& " ( TABLE.EXPIRATION_DATE <= 'datLast'" _
& " AND TABLE.EFFECTIVE_DATE >= 'datFirst' )" _
& " OR" _
& " ( ( TABLE.EXPIRATION_DATE >= 'datLast'" _
& " OR TABLE.EXPIRATION_DATE IS NULL ) " _
& " AND TABLE.EFFECTIVE_DATE < 'datLast' )" _
& " )" _
Query edited for clarity. Yes, I know that you can't put a variable directly into a literal date string, I just removed the text formatting to make it easier to read)
Does this capture EVERY code that was valid between datFirst and datLast, or am I missing something?