1 Finish Profile

MS Access ’97

Locked

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.

Topic

MS Access ’97

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.

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

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])

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

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

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

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

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

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

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

In reply to MS Access ’97

Assume you table [MyTable] has a structure something like below:

field01 – key
field02 – type
refdate01 – date
refdate02 – date
refdate03 – date
refdate04 – date
refdate05 – date
refdate06 – date
refdate07 – date
newfield – date
field(nn) – type

Go to Query | New Query |SQL View

update MyTable as A, MyTable as B
set a.newfield = b.refdate01
where a.field01 = b.field01;

[Note: Make sure you join all fields
in your primary key if you have a
complexe (more than one field) primary key]

Run the query ‘press the RED !’ – change the query to:

update MyTable as A, MyTable as B
set a.newfield = b.refdate02
where a.field01 = b.field01
and a.newfield < b.refdate02; Run the query - change the query to: update MyTable as A, MyTable as B set a.newfield = b.refdate03 where a.field01 = b.field01 and a.newfield < b.refdate03; Run the query - change the query to: update MyTable as A, MyTable as B set a.newfield = b.refdate04 where a.field01 = b.field01 and a.newfield < b.refdate04; Run the query - change the query to: update MyTable as A, MyTable as B set a.newfield = b.refdate05 where a.field01 = b.field01 and a.newfield < b.refdate05; Run the query - change the query to: update MyTable as A, MyTable as B set a.newfield = b.refdate06 where a.field01 = b.field01 and a.newfield < b.refdate06; Run the query - change the query to: update MyTable as A, MyTable as B set a.newfield = b.refdate07 where a.field01 = b.field01 and a.newfield < b.refdate07; Run the query - your're done. -------- Long term, I'd name each query above [Save As] and create a macro with an action of 'OpenQuery' and specify each query's name in the 'Query Name' attribute at the bottom of the screen, on each of the seven lines in the macro. Then simply run the macro when you want your table updated.

MS Access ’97

In reply to MS Access ’97

The question was auto-closed by TechRepublic

MS Access ’97

In reply to MS Access ’97

This question was auto closed due to inactivity

[Author]

Replies