Hi All

I have two fields on which I want to calculate an Urgency Field.

Fields:

Date1
Date2

If (Date1 < Date2) then Urgency would be 1
If (Date1 = Date2) then Urgency would be 2
If (Date1 < Date2 +4) AND (Date1 > Date2) then Urgency would be 3
if (Date1 > Date2 + 3) then Urgency would be 4

Can this be done in a calculated field and if so, what would the expression be?

Many thanks

Aaron

What you need to do is to create a public function

Try this

Public Function TestUrgency(LowerDate As Date, UpperDate As Date) As Integer

If LowerDate < UpperDate Then
TestUrgency = 1
Else If LowerDate = UpperDate Then
TestUrgency = 2
Else If LowerDate < DateAdd("d",4,UpperDate) And LowerDate > UpperDate Then
TestUrgency = 3
Else If LowerDate > DateAdd("d",3,UpperDate) Then
TestUrgency = 4
Else
TestUrgency = 0
End If

End Function

Note: I have added a further ugency of 0 this will detect if none of the conditions you set were met. Good for testing formulae.

You will also need to ensure that both dates are passed to the function.

To use this function in a query, add a new column and in the Field line:

Urgency:TestUrgency([YourDate1],[YourDate2])

David

Hi -

This can be done using the Switch() function. However, on the surface, this criteria doesn't appear to make sense.

If (Date1 < Date2 +4) AND (Date1 > Date2) then Urgency would be 3

Please provide an example.

Bob

Hi -

This can be done using the Switch() function. However, on the surface, this criteria doesn't appear to make sense.

If (Date1 < Date2 +4) AND (Date1 > Date2) then Urgency would be 3

Please provide an example.

Bob

I think that would cover Date1 = date2+1 or +2 or +3

Brian