Select the most recent date?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's one that's WAY beyond me.
I have 2 fields which include dates. One or both may be null as these dates
are "filled in" over time. I want to include a field in my report which
selects the more recent of the two dates & which remains empty if there ARE
no dates to choose from. For clarity, call them PointA, PointB, and
LatestPoint. I'm pretty sure Access has the "power" to do this, but I don't
have the wherewithal to tell it what I want!
 
Try typing something like this into your query:
LatestPoint: IIf([PointB] > [PointA], [PointB], [PointA])

That will fail if PointB has a value, but PointA is null.

If you actually have more than 2 date fields, you could use the MaxOfList()
function from this link:
http://allenbrowne.com/func-09.html
The calculated field would be:
LatestPoint: MaxOfList([PointA], [PointB])
 
I tried the link you suggested as I realize there are actually 3 points to
consider. Here's what I tried (using my actual field names):

=MaxOfList([PACE6],[PACE8],[Vaccine6])

When I try to run the report, I get an error message:

Syntax error (comma) in query expression
'MaxOfList([PACE6],[PACE8],[Vaccine6])'.

I tried to add an "=" in front of the expression & then when I try to run
the report, I get a "Enter Parameter Value" box asking for MaxOfList.

Ideas?

Again, thanks.

--
Thanks for your time!


Allen Browne said:
Try typing something like this into your query:
LatestPoint: IIf([PointB] > [PointA], [PointB], [PointA])

That will fail if PointB has a value, but PointA is null.

If you actually have more than 2 date fields, you could use the MaxOfList()
function from this link:
http://allenbrowne.com/func-09.html
The calculated field would be:
LatestPoint: MaxOfList([PointA], [PointB])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

NC_Sue said:
Here's one that's WAY beyond me.
I have 2 fields which include dates. One or both may be null as these
dates
are "filled in" over time. I want to include a field in my report which
selects the more recent of the two dates & which remains empty if there
ARE
no dates to choose from. For clarity, call them PointA, PointB, and
LatestPoint. I'm pretty sure Access has the "power" to do this, but I
don't
have the wherewithal to tell it what I want!
Click to expand...
Click to expand...
 
To debug this, open the Immediate Window (Ctrl+G).
Choose Compile in the Debug menu.
Fix any errors if it highlights anything.
Then test it by typing this expression into the Immediate Window:
? MaxOfList(2,4,3)
It should show 4.

Once that's working, open your query in design view.
In the Field row enter:
LatestPoint: MaxOfList([PACE6], [PACE8], [Vaccine6])
That should work, assuming that the 3 fields exist.
If you ask for a parameter, something is misspelt, or you do not have the
function in a standard module (one you can see on the Modules tab of the
Database window.)

Once you have that working, you can use the LatestPoint field in your
report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

NC_Sue said:
I tried the link you suggested as I realize there are actually 3 points to
consider. Here's what I tried (using my actual field names):

=MaxOfList([PACE6],[PACE8],[Vaccine6])

When I try to run the report, I get an error message:

Syntax error (comma) in query expression
'MaxOfList([PACE6],[PACE8],[Vaccine6])'.

I tried to add an "=" in front of the expression & then when I try to run
the report, I get a "Enter Parameter Value" box asking for MaxOfList.

Ideas?

Again, thanks.

--
Thanks for your time!


Allen Browne said:
Try typing something like this into your query:
LatestPoint: IIf([PointB] > [PointA], [PointB], [PointA])

That will fail if PointB has a value, but PointA is null.

If you actually have more than 2 date fields, you could use the
MaxOfList()
function from this link:
http://allenbrowne.com/func-09.html
The calculated field would be:
LatestPoint: MaxOfList([PointA], [PointB])

NC_Sue said:
Here's one that's WAY beyond me.
I have 2 fields which include dates. One or both may be null as these
dates
are "filled in" over time. I want to include a field in my report which
selects the more recent of the two dates & which remains empty if there
ARE
no dates to choose from. For clarity, call them PointA, PointB, and
LatestPoint. I'm pretty sure Access has the "power" to do this, but I
don't
have the wherewithal to tell it what I want!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top