Multiple Date Fields - Determine Most Recent

[cfmiles]

I don't know if this can be done... I sure hope it can!

I have 6 date fields(D1, D2, D3, D4, D5, D6). I want to determine the most recent date in the fields and populate a new field (Drecent). A couple of wrenches to throw in... Any date field can have a null value. Multiple date fields (for same record) can have the same date.

We have figured out how to do it if there are no nulls and all dates are different in a record - unfortunately - that is not the case.

Can this be done? Can it be done with SQL (no VBA?). If not, VBA will be fine.

Thanks in advance!

 

[Pat Hartman]

See my answer to this poster:
http://www.access-programmers.co.uk/ubb/Forum3/HTML/002378.html

Your situation is identical.

 

[cfmiles]

Thanks for the response, however, I don't follow. I'm pretty sure my situation is completely different.

I am using access as a front end to an Oracle database, so I have no control over table structure. I have to find a way to do this within a single query (for multiple reasons) if it is possible. I can't use union queries and need to avoic VBA. Lots of restrictions, I know! See why I'm having trouble?

 

[Pat Hartman]

Your two choices are a union query and VBA. Choose your poison. Actually, you have a third. You could always write an Oracle stored procedure.

 

[Chris RR]

Are you trying to save the value of the most recent date, or the name of the field that contains the most recent date? Are you really trying to store this value in the record (hope not, very bad idea) or use it somewhere?

I'd suggest a good Oracle reference book and a chat with your DBA.