Difficult Calculation

[Agent_KGB]

Hello, i have a table with "Contract Date" and "State" fields... what i need
to calculate is whether or not each record falls into bellow categories:

If state = NY and contract date > year from today then assign CODE1 in the
field
if state = NY and contract date > today but < year from now then assign
CODE2 in the field
if State = NY and contract Date <= Today then assign CODE3 in the field
if state = WA...
if State = CA...

so in essence i have 3 codes for each state...

i am really stumped on how to handle this... any advice would be much
appreciated.

 

[Marshall Barton]

Hello, i have a table with "Contract Date" and "State" fields... what i need
to calculate is whether or not each record falls into bellow categories:

If state = NY and contract date > year from today then assign CODE1 in the
field
if state = NY and contract date > today but < year from now then assign
CODE2 in the field
if State = NY and contract Date <= Today then assign CODE3 in the field
if state = WA...
if State = CA...

so in essence i have 3 codes for each state...

If the date range and codes are the same for every state,
then the state and code doesn't enter into it and you can
use the calculated field:

Code: IIf(contractdate > DateAdd("yyyy", 1, Date()),
"CODE1", IIf(contractdate > Date(), CODE2, "CODE3"))

If some states can have different a date range and/or
different codes, then I suggest using another table to hold
the various date range values and codes. Then you can use a
non-equal join to connect each record to its state, date
range values and codes.

 

[KARL DEWEY]

State has no bearing on what you want.
Category: IIF([Contract Date] <= Date(), "CODE3", IIF([Contract Date] >
Date() AND < DateAdd("yyyy", 1, Date(), "CODE2", "CODE1"))

 

[Daryl S]

If the codes are different for each state, then you should set up a table for
the codes. The table should contain the state, the date range criteria, and
the resulting code. Since you cannot put in fixed date ranges, you can use a
text string or if you prefer some other values. You can help enforce the
values at the table level by limiting the choices to the three values. This
sample table assumes three text strings:

State DateRange StateDateRangeCode
NY BeforeToday CODE1
NY MoreThanYear CODE3
NY WithinYear CODE2
WA BeforeToday CODE4
WA MoreThanYear CODE6
WA WithinYear CODE5
.... etc.

Then build a function to determine the text string based on today's date.
Of course these must use the same text strings:

Public Function CompareContractDate(inContractDate As Date) As String
'Compares the contract date passed in to today, returning one of three
strings.
Dim strCompare As String

strCompare = ""

If inContractDate < Now() Then
strCompare = "BeforeToday"
Else
If DateAdd("d", 365, Now()) < inContractDate Then
strCompare = "MoreThanYear"
Else
strCompare = "WithinYear"
End If
End If

CompareContractDate = strCompare

End Function


The advantage to the function is that you can use it anywhere - in queries,
reports, on forms, etc.

Now the query is easy. This assumes the information you want to analyze is
in a table called Contracts, and the needed fields are State and Contract
Date:

SELECT Contracts.Contract, Contracts.State, Contracts.[Contract Date],
StateDateRangeCodes.StateDateRangeCode, CompareContractDate([Contract Date])
AS DateToNow
FROM Contracts LEFT JOIN StateDateRangeCodes ON Contracts.State =
StateDateRangeCodes.State
WHERE (((CompareContractDate([Contract Date]))=[DateRange]));


I hope this helps!

 

[Agent_KGB]

I think this is exactly what i needed... just need to test it first... but
from the look of it, you nailed it right on the head... thanks Daryl!!!

If the codes are different for each state, then you should set up a table for
the codes. The table should contain the state, the date range criteria, and
the resulting code. Since you cannot put in fixed date ranges, you can use a
text string or if you prefer some other values. You can help enforce the
values at the table level by limiting the choices to the three values. This
sample table assumes three text strings:

State DateRange StateDateRangeCode
NY BeforeToday CODE1
NY MoreThanYear CODE3
NY WithinYear CODE2
WA BeforeToday CODE4
WA MoreThanYear CODE6
WA WithinYear CODE5
... etc.

Then build a function to determine the text string based on today's date.
Of course these must use the same text strings:

Public Function CompareContractDate(inContractDate As Date) As String
'Compares the contract date passed in to today, returning one of three
strings.
Dim strCompare As String

strCompare = ""

If inContractDate < Now() Then
strCompare = "BeforeToday"
Else
If DateAdd("d", 365, Now()) < inContractDate Then
strCompare = "MoreThanYear"
Else
strCompare = "WithinYear"
End If
End If

CompareContractDate = strCompare

End Function


The advantage to the function is that you can use it anywhere - in queries,
reports, on forms, etc.

Now the query is easy. This assumes the information you want to analyze is
in a table called Contracts, and the needed fields are State and Contract
Date:

SELECT Contracts.Contract, Contracts.State, Contracts.[Contract Date],
StateDateRangeCodes.StateDateRangeCode, CompareContractDate([Contract Date])
AS DateToNow
FROM Contracts LEFT JOIN StateDateRangeCodes ON Contracts.State =
StateDateRangeCodes.State
WHERE (((CompareContractDate([Contract Date]))=[DateRange]));


I hope this helps!
--
Daryl S

Hello, i have a table with "Contract Date" and "State" fields... what i need
to calculate is whether or not each record falls into bellow categories:

If state = NY and contract date > year from today then assign CODE1 in the
field
if state = NY and contract date > today but < year from now then assign
CODE2 in the field
if State = NY and contract Date <= Today then assign CODE3 in the field
if state = WA...
if State = CA...

so in essence i have 3 codes for each state...

i am really stumped on how to handle this... any advice would be much
appreciated.

 

[Agent_KGB]

Actually state does have a bearing on it...

there are 3 CODEs assigned to each state... so for state NY it's CODE1,
CODE2, CODE3 (Based on the dates), for WA there is CODE4, CODE5 and CODE6
(one for each timefram)... etc...

Sorry for not making it clearer in my original post.

Stan

State has no bearing on what you want.
Category: IIF([Contract Date] <= Date(), "CODE3", IIF([Contract Date] >
Date() AND < DateAdd("yyyy", 1, Date(), "CODE2", "CODE1"))

--
Build a little, test a little.

Hello, i have a table with "Contract Date" and "State" fields... what i need
to calculate is whether or not each record falls into bellow categories:

If state = NY and contract date > year from today then assign CODE1 in the
field
if state = NY and contract date > today but < year from now then assign
CODE2 in the field
if State = NY and contract Date <= Today then assign CODE3 in the field
if state = WA...
if State = CA...

so in essence i have 3 codes for each state...

i am really stumped on how to handle this... any advice would be much
appreciated.