Multiple Dates

[kitty77]

I'm using the following code in a query but can't seem to get what I need...

Date: IIf(Not IsNull([Manalysisdate1]),[Manalysisdate1],IIf(Not IsNull([Manalysisdate2]),[Manalysisdate2],IIf(Not IsNull([Manalysisdate3]),[Manalysisdate3],IIf(Not IsNull([Manalysisdate4]),[Manalysisdate4]))))

Thanks...


if it looks like this, use Manalysisdate4

Manalysisdate1​	Manalysisdate2​	Manalysisdate3​	Manalysisdate4​
text text text​	text text text​	text text text​	text text text​

if it looks like this, use Manalysisdate3

Manalysisdate1​	Manalysisdate2​	Manalysisdate3​	​
text text text​	text text text​	text text text​	​

if it looks like this, use Manalysisdate2

Manalysisdate1​	Manalysisdate2​	​	​
text text text​	text text text​	​	​

if it looks like this, use Manalysisdate1

Manalysisdate1​	​	​	​
text text text​	​	​	​

 

[GPGeorge]

Bummer of a table design. Four repeating fields? Manalysisdate1, Manalysisdate2, Manalysisdate3, Manalysisdate4.

That sort of "spreadsheet style" table makes querying so much harder! Would now be a good time to correct that table design flaw? Or do you already have hundreds of records added into the table? If you can correct it, here is an excellent set of Blog Posts on the problem and how to go about correcting it.

On the other hand, sometimes it's harder to revise to a better design than to muddle along with one like this.

For starters, let's figure out what kind of data is in those fields. The names suggest Dates? But the example shows text. What do we have to work with, actually?

 

[kitty77]

Null. Thanks!