Multiple Dates (1 Viewer)

kitty77

Registered User.
Local time
Today, 04:12
Joined
May 27, 2019
Messages
693
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

Grover Park George
Local time
Today, 01:12
Joined
Nov 25, 2004
Messages
1,776
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

Nifty Access Guy
Staff member
Local time
Today, 08:12
Joined
Jul 9, 2003
Messages
16,245
You need to put all the dates in one field, say:-

Manalysisdate
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:12
Joined
Feb 19, 2002
Messages
42,981
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"))))
 

Users who are viewing this thread

Top Bottom