Calculated Field

[OldGuy]

I am trying to devise a calculated field (Epires with a formula/function
for the following account types. Type 1 expires 1 year from issue date
(9/1/07 expires 8/31/08). Type 2 expires, not to exceed, 6 years form issue
date on 9/30/XX (9/1/03 expires 9/30/08, 10/12/07 expires 9/30/12, 7/31/07
expires 9/30/12). Type 3 does not expire. The result needs to be a date
format that I can use for notices and reports. Can anyone help. Thank you
in advance.


AccountType Issued Date Expires
2 9/1/2003
2 6/1/2006
1 9/1/2007
1 11/29/2007
1 4/11/2007
2 10/12/2007
3 7/31/2007

 

[KARL DEWEY]

Use a translation table --
AccountDuration --
AccountType Duration
1 1
2 6
3 99

and edit this query as necessary substituting your table and
field names.
SELECT *, DateAdd("yyyyy", [Duration], [Issued Date]) AS [Expires]
FROM [YourTable], [AccountDuration]
WHERE [AccountDuration].[AccountType] = [YourTable].[AccountType];

 

[Klatuu]

Try this:
Call it from your query in the calculated field:
Expires: ExpiresOn([AccountType], [IssueDate])
But, of course, use your actual field names

Public Function ExpiresOn(AccountType As Long, IssueDate As Date) As String
Dim dtmTestDate As Date

Select Case AccountType
Case 1
ExpiresOn = Format(DateAdd("d", -1, DateAdd("yyyy", 1,
IssueDate)), _
"short date")
Case 2
dtmTestDate = DateSerial(Year(IssueDate) + 6, 9, 30)
If Month(dtmTestDate) >= 9 Then
ExpiresOn = Format(DateAdd("yyyy", -1, dtmTestDate), "short
date")
Else
ExpiresOn = Format(DateAdd("yyyy", -1, dtmTestDate), "short
date")
End If
Case 3
ExpiresOn = "Does Not Expire"
End Select
End Function

 

[KARL DEWEY]

OR with the translation table in your query use DLookup like this (I may
not have syntax correct) --
Expires: DateAdd("yyyy",
DLookUp("[Duration]","AccountDuration","[AccountDuration].[AccountType] =
[YourTableName].[AccountType]")

--
KARL DEWEY
Build a little - Test a little

Use a translation table --
AccountDuration --
AccountType Duration
1 1
2 6
3 99

and edit this query as necessary substituting your table and
field names.
SELECT *, DateAdd("yyyyy", [Duration], [Issued Date]) AS [Expires]
FROM [YourTable], [AccountDuration]
WHERE [AccountDuration].[AccountType] = [YourTable].[AccountType];
--
KARL DEWEY
Build a little - Test a little

I am trying to devise a calculated field (Epires with a formula/function
for the following account types. Type 1 expires 1 year from issue date
(9/1/07 expires 8/31/08). Type 2 expires, not to exceed, 6 years form issue
date on 9/30/XX (9/1/03 expires 9/30/08, 10/12/07 expires 9/30/12, 7/31/07
expires 9/30/12). Type 3 does not expire. The result needs to be a date
format that I can use for notices and reports. Can anyone help. Thank you
in advance.


AccountType Issued Date Expires
2 9/1/2003
2 6/1/2006
1 9/1/2007
1 11/29/2007
1 4/11/2007
2 10/12/2007
3 7/31/2007

 

[OldGuy]

Thank you. It worked well first time. One more question, if you don't mind.
In the if, then else statement, the if and the else are the same. Should I
change one? Thanks again, I do appreciate it.

Try this:
Call it from your query in the calculated field:
Expires: ExpiresOn([AccountType], [IssueDate])
But, of course, use your actual field names

Public Function ExpiresOn(AccountType As Long, IssueDate As Date) As String
Dim dtmTestDate As Date

Select Case AccountType
Case 1
ExpiresOn = Format(DateAdd("d", -1, DateAdd("yyyy", 1,
IssueDate)), _
"short date")
Case 2
dtmTestDate = DateSerial(Year(IssueDate) + 6, 9, 30)
If Month(dtmTestDate) >= 9 Then
ExpiresOn = Format(DateAdd("yyyy", -1, dtmTestDate), "short
date")
Else
ExpiresOn = Format(DateAdd("yyyy", -1, dtmTestDate), "short
date")
End If
Case 3
ExpiresOn = "Does Not Expire"
End Select
End Function
--
Dave Hargis, Microsoft Access MVP

I am trying to devise a calculated field (Epires with a formula/function
for the following account types. Type 1 expires 1 year from issue date
(9/1/07 expires 8/31/08). Type 2 expires, not to exceed, 6 years form issue
date on 9/30/XX (9/1/03 expires 9/30/08, 10/12/07 expires 9/30/12, 7/31/07
expires 9/30/12). Type 3 does not expire. The result needs to be a date
format that I can use for notices and reports. Can anyone help. Thank you
in advance.


AccountType Issued Date Expires
2 9/1/2003
2 6/1/2006
1 9/1/2007
1 11/29/2007
1 4/11/2007
2 10/12/2007
3 7/31/2007