Select smallest date or null value

[jeffshex]

Hey all,

I have a select statement that I'm using in a query column to return the smallest date using the Min function. The problem I'm running into is I need NULL to be the "smallest" value.

For example I have this query:

MinEndDate: (SELECT Min(EndDate) FROM tblPlacementData b WHERE b.UserID = tblPlacementData.UserID)

Not sure how to implement that piece of functionality into that or what.
My end goal is to supply the single value of either the Min(EndDate) or NULL if it exists.

Thanks!!!

 

[Remou]

Nz may suit:

Min(Nz(EndDate,0))

http://lessthandot.com

 

[jeffshex]

That works, just introduces one more issue however.

My main query has 2 calc fields:

DaysUnemp: [StartDate]-Nz([MaxEndDate],[StartDate])
---
MaxEndDate: (SELECT Max(EndDate) FROM tblPlacementData b WHERE b.UserID = tblPlacementData.UserID AND b.EndDate <= tblPlacementData.StartDate)

Essentially I'm looking at gaps of employment records for each individual. Is what makes it tough (and this is what I'm trying to solve), is the fact that is if someone has 1 current job and multiple previous ones.

If someone has the following:

UserID	Company  StartDate  EndDate  MaxEndDate  DaysUnemp
==========================================================
333     A        10/3/04    2/3/05               0
333     B        2/28/05    11/17/05 2/3/05      25
333     C        12/27/05   4/27/06  11/17/05    40
333     D        8/21/06    9/15/06  4/27/06     116
333     E        11/13/06            9/15/06     59
===========================================================

That works fine for me unless they are still at company A.
It's tough to explain, but the end game is trying to find out if a person is retained at employment, a simple yes no.
The catch is that they are allowed to go between jobs within a 90 day window.
The whole working 2 jobs just throws a kink in it all.
[/code]