General discussion

Locked

MS Access '97

By tlefort ·
I have 7 fields that contain dates that reflect different stages of a project. These dates correspond to reference numbers. I want to fill a newly created field with the most recent reference number by finding the most recent date. I'm trying to use If...Then...Else statements, but am not having much success. I need a solution fairly quickly. Please help! Meanwhile, I'll have my nose in my Access books, which haven't helped thus far.

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Access '97

by bugars In reply to MS Access '97

Try MAX(a1, b1, c1, etc) and make the result a date as well. It will find the largest date, which should also be the most recent.

Collapse -

MS Access '97

by tlefort In reply to MS Access '97

The question was auto-closed by TechRepublic

Collapse -

MS Access '97

by malcolm.sargeant In reply to MS Access '97

You have to create your own function for this one
add this to a new module assuming you have four dates to compare

Function maximum(a As Date, b As Date, c As Date, d As Date) As Date
maximum = a
If b >= maximum Then maximum = b
If c >= maximum Then maximum = c
If d >= maximum Then maximum = d
End Function

then in your query to find the max date put the following

maxdate:maximum([field1],[field2],[field3],[field4])

Collapse -

MS Access '97

by tlefort In reply to MS Access '97

The question was auto-closed by TechRepublic

Collapse -

MS Access '97

by M.R.Chambers In reply to MS Access '97

You could try typing the following as the Control Source of the new field:

=iif(a1>b1,[a1reference],iif(b1>c1,[b1reference],iif(c1>d1,[c1reference],[d1reference])))

It
s fairly cumbersome and may need some work, but could word.

M.R.Chambers

Collapse -

MS Access '97

by tlefort In reply to MS Access '97

The question was auto-closed by TechRepublic

Collapse -

MS Access '97

by M.R.Chambers In reply to MS Access '97

You could try typing the following as the Control Source of the new field:

=iif(a1>b1,[a1reference],iif(b1>c1,[b1reference],iif(c1>d1,[c1reference],[d1reference])))

It's fairly cumbersome and may need some work, but could word.

M.R.Chambers

Collapse -

MS Access '97

by tlefort In reply to MS Access '97

The question was auto-closed by TechRepublic

Collapse -

MS Access '97

by Oliver W. In reply to MS Access '97

Hi!
Use the DMAX function.
DMax (?expr?; ?domain?; ?criteria?)
Assuming the Field is called [DATE] and the table is [PROJECTS], then using
DMax ("[DATE]";"[PROJECTS]";"") will return the maximum (most recent) date.

hope that helps,
Oliver

Collapse -

MS Access '97

by tlefort In reply to MS Access '97

The question was auto-closed by TechRepublic

Back to Community Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums