Hi,

I'm trying to get a validation formula working, but for some reason keeps throwing up incorrect data.  here's what I'm trying to achieve:

I have an SQL database connection established created for a report to query how long an item has been hired for. to the report I pass a from date and to date 'todate' for the period I need to report against.  The 'todate' then needs to be evaluated against the order date and return date (which could be null is not returned) in the database.  In priciple here's my rules:

if todate >= orderdate then it should be 'todate'
if returndate is null then it should be 'todate'
if todate = returndate then it should be 'todate' (it could be returndate also)
if todate < returndate then it should be returndate

I have 2 iterations:

The first, if I run it returns a date fal all items where there is a null returndate of it = the return date.  All other results are blank.

if isnull({tblOrderDet.Return Date}) or {?ToDate} <= {tblOrderDet.Return Date}
and {?ToDate} >= {tblOrders.OrderDate} then
    {?ToDate}
    else
    date (0,0,0)

The second, if I run this one, will return the correct dates on all rows, but even rows that a should be receiving a blank value.

if isnull({tblOrderDet.Return Date}) or {?ToDate} <= {tblOrderDet.Return Date}
and {?ToDate} >= {tblOrders.OrderDate} then
    {?ToDate}
    else
    if {?ToDate} > {tblOrders.OrderDate} then
    {tblOrderDet.Return Date}
    else
    date (0,0,0)

I can't quite see the wood through the trees with this - any help would be greatly appreciated...

Simon