Filing out the empty colums

[Aietoe]

Hi!

Using Access97, i have a table that looks like this:

Name date1 date2 date3
BOUNxx 00-10-10 00-10-12 00-10-14
COLDxx 00-10-10
CYRPxx 00-10-12
SCIFxx 00-10-14
How can i change it to this:

Name date1 date2 date3
BOUNxx 00-10-10 00-10-12 00-10-14
COLDxx 00-10-10
CYRPxx 00-10-12
SCIFxx 00-10-14

Thanks

Aietoe

 

[smedvid]

Essentially, you need to use an IIF() to check to see if date1 or date2 is null. In a hurry, so here is some quick Psuedo code....
IIF(IsNull(Date1),IIF(IsNull(Date2),Date3,Date2),Date1)

htwh

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[larryww]

I'm vegging on the "neat" solution. Sorting (descending) and using an update querie works for those with one date field, but leaves the ones with more than one field descending.

If your are assured to be limited to the 3 date fields, you could brute force it. I'm also assuming the possibility that a record could have exactly 2 dates.

Newdate1: iif( isnull(date1), iif( isnull(date2), date3, date2), date1 )
Newdate2: iif( isnull(date1), iif( isnull(date2), Null, date3), date2 )
Newdate3: iif( isnull(date1), iif( isnull(date2), Null, Null), date3 )

Be sure to check my logic - I haven't exhaustively done so.

You could also brute force it in a "bubble" fashion, iteratively moving dates to the left when there was nothing to its left. This could be more easily generalized for any number of fields, rather than just 3:
1st query:
Newdate2: isnull(date2), date3, date2)
Newdate3: isnull(date2), Null, date3)
next query:
Newdate1: isnull(date1), date2, date1)
Newdate2: isnull(date1), Null, date2)

Again, check this before staking your career or livelihood on it