Community

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.

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

Related Discussions

Related Forums