Module coding

[Lurch]

Hi there. I sure hope you can help me as I've wasted a ton of time.

I have four date Fields...one may have a date, or two up to all four.
call them Date1, Date2, Date3, Date4. These are dates that my project
was approved but based upon it's cost the approval goes 'up the line'
to Date4 if the big guy's approval was needed.

Let's say that I want to know when the "Absolute" Approval was given.
How would I code a function so that it would look at Date4, if there
is a date put that value in the field [dtmApproved_Final] of
'tbl_Some_Table' if nothing is in Date4 then go to Date3. if there is
a date put that value in the field [dtmApprove_Final]. If nothing is
in Date3 then go to Date2 and so on.

Now, I want to use a macro to run that function. How do I get the
function to update the [dtmApproved_Fina] field in 'tbl_Some_Table'.

Any help would be GREATLY appreciated.

Thanks Guys!!
Tony

 

[Gerald Stanley]

I assume that the 4 date fields are unbound. Put a text
box control (txt_dtmApproved_Final) on the form that is
bound to dtmApproved_Final and make it invisible.
Then, put code along the following lines in the form's
Before_Update eventhandler

If Not IsNull(Date4) then
txt_dtmApproved_Final = Date4
ElseIf Not IsNull(Date3) Then
txt_dtmApproved_Final = Date3
ElseIf Not IsNull(Date2) Then
txt_dtmApproved_Final = Date2
ElseIf Not IsNull(Date1) Then
txt_dtmApproved_Final = Date1
End If

Help This Helps
Gerald Stanley MCSD

 

[TC]

As a general rule you should not store "derived" values in a database. A
derived value is a value that can be calculated, when required, from other
values that >are< stored within the database.

Date of final approval is derived from the other 4 dates. So, store those
other 4 dates, and derive or calculate the date of final approval, when
required. For example:

(untested)

SELECT Date1, Date2, Date3, Date4,
iif (not isnull(date4), date4,
iif (not isnull(date3), date3,
iif (not isnull(date2), date2,
iif (not isnull(date1), date1), Null))))
AS [Final Approval]

or you could pass the 4 dates to a function that used "if" tests to select
the right date - thus avoiding the nasty nested iif's.

HTH,
TC