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?

 

[Uncle Gizmo]

You need to put all the dates in one field, say:-

Manalysisdate

 

[Pat Hartman]

Do NOT name the final field "Date". Date is the name of a function and is therefore a reserved word. If you reference it in code, how will Access know if you mean your date or the date function. The answer is it has no way of knowing so it always assumes you mean your date so you won't ever be able to get today's date in any procedure in a form bound to this query.

You are also missing the value you want when all the dates are null:

Date: IIf(Not IsNull([Manalysisdate1]),[Manalysisdate1],IIf(Not IsNull([Manalysisdate2]),[Manalysisdate2],IIf(Not IsNull([Manalysisdate3]),[Manalysisdate3],IIf(Not IsNull([Manalysisdate4]),[Manalysisdate4],"what do you want in this case"))))

 

[kitty77]

Null. Thanks!