Alternative to compound IFF statement?

[wellmabt]

Currently I have about 20 years worth of data and each record has an
occurence date. I want to append a column to the table that gives the policy
year. The only way I currently know how to do this is by using a compound
iff statement like:

iff([date] between #10/1/1995# and #9/30/1996#, 1995, iff([date] between
#10/1/1996# and #9/30/1997#, 1997, ...

and I would have to continue this on for 20 years worth. Any ideas of a
better way to do this?

Thanks!
Brian

 

[KARL DEWEY]

An eaiser way is to calculate the date field like this --
Policy_Year: Year(DateAdd("m", -9, [YourDateField]))

The above is based on your posted iff([date] between #10/1/1995# and
#9/30/1996#, 1995....

Does your policy year lag by 9 months or begin 3 months early?

If your post should have been iff([date] between #10/1/1994# and
#9/30/1995#, 1995.... then it would be ---
Policy_Year: Year(DateAdd("m", 3, [YourDateField]))

 

[fredg]

Currently I have about 20 years worth of data and each record has an
occurence date. I want to append a column to the table that gives the policy
year. The only way I currently know how to do this is by using a compound
iff statement like:

iff([date] between #10/1/1995# and #9/30/1996#, 1995, iff([date] between
#10/1/1996# and #9/30/1997#, 1997, ...

and I would have to continue this on for 20 years worth. Any ideas of a
better way to do this?

Thanks!
Brian

Does this help?

PolicyYear: IIf(Month([ADate])<10,Year([ADate])-1,Year([ADate]))

Note: the name of the function is IIF not IFF.

But why do you need to add this information to your table.
As long as you have the PolicyDate stored, anytime you need the policy
year all you need do is run the above calculation, in a Report or on a
Form, using an unbound text control:

=IIf(Month([ADate])<10,Year([ADate])-1,Year([ADate]))

Storing derived data like this goes against database normalization
rules.

Also, if the name of the field is really "Date", then
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.

For a more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Jerry Whittle]

In a query:

ThePolicyYear: IIf(Month([date]) > 9, Year([date])+1, Year([date]))

I believe that your example has a problem as the #9/30/1996# date would
return a year of 1995.

Also Date is a reserved word in Access. Always make sure to surround it with
square brackets [ ]. For more on reserved words to avoid, see:
http://support.microsoft.com/kb/286335/

MOST IMPORTANT POINT: You shouldn't add a column to your table. Rather you
should uses something like above in a query, form, or report to get the
Policy Year as needed. What would happen if someone corrected a mistaken
date? Your fiscal year column could now be incorrect and not match the dates.

 

[wellmabt]

Thanks Jerry, a couple of follow-up questions...

another issue is that the policy periods change half-way through: for example

period policy year(this is what I want it to
return)
10/1/1975 - 9/30/1976 1975
..
..
..
10/1/1998 - 9/30/1999 1998
10/1999 - 5/31/2000 1999
6/1/2000 - 5/31/2001 2000
6/1/2001 - 5/31/2002 2001
..
..
..
How can I make this work?


And sorry, the date column is in fact named [adate].

Thanks!
Brian

In a query:

ThePolicyYear: IIf(Month([date]) > 9, Year([date])+1, Year([date]))

I believe that your example has a problem as the #9/30/1996# date would
return a year of 1995.

Also Date is a reserved word in Access. Always make sure to surround it with
square brackets [ ]. For more on reserved words to avoid, see:
http://support.microsoft.com/kb/286335/

MOST IMPORTANT POINT: You shouldn't add a column to your table. Rather you
should uses something like above in a query, form, or report to get the
Policy Year as needed. What would happen if someone corrected a mistaken
date? Your fiscal year column could now be incorrect and not match the dates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Currently I have about 20 years worth of data and each record has an
occurence date. I want to append a column to the table that gives the policy
year. The only way I currently know how to do this is by using a compound
iff statement like:

iff([date] between #10/1/1995# and #9/30/1996#, 1995, iff([date] between
#10/1/1996# and #9/30/1997#, 1997, ...

and I would have to continue this on for 20 years worth. Any ideas of a
better way to do this?

Thanks!
Brian

 

[Bob Barrows [MVP]]

Thanks Jerry, a couple of follow-up questions...

another issue is that the policy periods change half-way through:
for example

period policy year(this is what I want it
to return)
10/1/1975 - 9/30/1976 1975
.
.
.
10/1/1998 - 9/30/1999 1998
10/1999 - 5/31/2000 1999

So 1999 only consists of 6 months?
..

6/1/2000 - 5/31/2001 2000
6/1/2001 - 5/31/2002 2001
.
.
.
How can I make this work?

Well, that seems to be pretty arbitrary. My advice would be to create a
Calendar table with two fields: CalendarDate, PolicyYear. Populate it
with all the dates from 10/1/1995 to 9/30/2015. Then, if the rules
change again, all you do is change the data in the PolicyYear column in
this table.

With the Calendar table, all you do is join your original table to the
calendar table in a query using the adate field.

Give me a while and I can come up with a VBA procedure to create and
populate this table per your current rules

 

[KARL DEWEY]

This should handle it --
Policy_Year: IIF([aDate] <#6/1/2000#, Year(DateAdd("m", -9, [aDate])),
Year(DateAdd("m", -6, [aDate])))


--
KARL DEWEY
Build a little - Test a little

Thanks Jerry, a couple of follow-up questions...

another issue is that the policy periods change half-way through: for example

period policy year(this is what I want it to
return)
10/1/1975 - 9/30/1976 1975
.
.
.
10/1/1998 - 9/30/1999 1998
10/1999 - 5/31/2000 1999
6/1/2000 - 5/31/2001 2000
6/1/2001 - 5/31/2002 2001
.
.
.
How can I make this work?


And sorry, the date column is in fact named [adate].

Thanks!
Brian

In a query:

ThePolicyYear: IIf(Month([date]) > 9, Year([date])+1, Year([date]))

I believe that your example has a problem as the #9/30/1996# date would
return a year of 1995.

Also Date is a reserved word in Access. Always make sure to surround it with
square brackets [ ]. For more on reserved words to avoid, see:
http://support.microsoft.com/kb/286335/

MOST IMPORTANT POINT: You shouldn't add a column to your table. Rather you
should uses something like above in a query, form, or report to get the
Policy Year as needed. What would happen if someone corrected a mistaken
date? Your fiscal year column could now be incorrect and not match the dates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Currently I have about 20 years worth of data and each record has an
occurence date. I want to append a column to the table that gives the policy
year. The only way I currently know how to do this is by using a compound
iff statement like:

iff([date] between #10/1/1995# and #9/30/1996#, 1995, iff([date] between
#10/1/1996# and #9/30/1997#, 1997, ...

and I would have to continue this on for 20 years worth. Any ideas of a
better way to do this?

Thanks!
Brian

 

[Bob Barrows [MVP]]

Thanks Jerry, a couple of follow-up questions...

another issue is that the policy periods change half-way through:
for example

period policy year(this is what I want it
to return)
10/1/1975 - 9/30/1976 1975
.
.
.
10/1/1998 - 9/30/1999 1998
10/1999 - 5/31/2000 1999
6/1/2000 - 5/31/2001 2000
6/1/2001 - 5/31/2002 2001
.
.

Here is a procedure to create and populate the Calendar table using
these rules. Paste it into a module, make sure you go to
Tools>References and set a reference to the Microsoft DAO 3.6 library,
then run it:

Sub FillCalendarTable()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim sql As String, d As Date, errnum As Long
Set db = CurrentDb
On Error Resume Next
Set tdf = db.TableDefs("Calendar")
errnum = Err
Err.Clear
On Error GoTo 0
If errnum <> 0 Then
sql = "create table Calendar (" & _
"CalendarDate datetime " & _
"constraint PK_Calendar PRIMARY KEY, " & _
"PolicyYear SHORT)"
db.Execute sql
End If
sql = "insert into calendar Values([p1],[p2])"
Set qdf = db.CreateQueryDef("", sql)
d = #10/1/1995#
Do Until d = #6/1/2000#
qdf(0) = d
qdf(1) = Year(DateAdd("m", -9, d))
qdf.Execute
d = d + 1
Loop
Do Until d = #6/1/2015#
qdf(0) = d
qdf(1) = Year(DateAdd("m", -5, d))
qdf.Execute
d = d + 1
Loop
Set qdf = Nothing
Set db = Nothing
End Sub

After running this procedure, you can create a query like this:
Select policy, adate,policyyear
from policies join calendar on adate=calendardate

 

[raskew via AccessMonster.com]

Hi Bob -

The procedure errs on this line.
sql = "insert into calendar Values([p1],[p2])"

Best as I can tell, [p1] and [p2] are undefined.

Best wishes - raskew

Thanks Jerry, a couple of follow-up questions...

[quoted text clipped - 13 lines]

.
.

Here is a procedure to create and populate the Calendar table using
these rules. Paste it into a module, make sure you go to
Tools>References and set a reference to the Microsoft DAO 3.6 library,
then run it:

Sub FillCalendarTable()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim sql As String, d As Date, errnum As Long
Set db = CurrentDb
On Error Resume Next
Set tdf = db.TableDefs("Calendar")
errnum = Err
Err.Clear
On Error GoTo 0
If errnum <> 0 Then
sql = "create table Calendar (" & _
"CalendarDate datetime " & _
"constraint PK_Calendar PRIMARY KEY, " & _
"PolicyYear SHORT)"
db.Execute sql
End If
sql = "insert into calendar Values([p1],[p2])"
Set qdf = db.CreateQueryDef("", sql)
d = #10/1/1995#
Do Until d = #6/1/2000#
qdf(0) = d
qdf(1) = Year(DateAdd("m", -9, d))
qdf.Execute
d = d + 1
Loop
Do Until d = #6/1/2015#
qdf(0) = d
qdf(1) = Year(DateAdd("m", -5, d))
qdf.Execute
d = d + 1
Loop
Set qdf = Nothing
Set db = Nothing
End Sub

After running this procedure, you can create a query like this:
Select policy, adate,policyyear
from policies join calendar on adate=calendardate

 

[Lord Kelvan]

regardless of what you do because of what you are trying to do you
should use a switch insted of a iif

switch(datefield between val1 and val2,"output",datefield between val3
and val4 ,"output",etc)

 

[Bob Barrows [MVP]]

Hi Bob -

The procedure errs on this line.
sql = "insert into calendar Values([p1],[p2])"

Really? What's the error? That line is a simple assignment of a string to a
variable ...

Best as I can tell, [p1] and [p2] are undefined.

That's strange. I tested and ran it on my machine. Why would it care what
characters are in the string being assigned?
Let me try it again on my home machine (A2003) here ...
....
No, it runs without error. I'm not sure what's happening for you?

 

[raskew via AccessMonster.com]

My possible error. I received the following when running the procedure in
A97

Run-time error '3127':
The INSERT INTO statement contains the following unknown field name: 'p1'

Run in A2003 it processes as advertised.

Values is not something I ever use and am a tad confused about your usage.
Have been searching A2003 Help File for explanation/example(s).

If you shed some light on the subject, it'd be most appreciated.

Best wishes - raskew

Hi Bob -

The procedure errs on this line::
sql = "insert into calendar Values([p1],[p2])"

Really? What's the error? That line is a simple assignment of a string to a
variable ...

Best as I can tell, [p1] and [p2] are undefined.

That's strange. I tested and ran it on my machine. Why would it care what
characters are in the string being assigned?
Let me try it again on my home machine (A2003) here ...
...
No, it runs without error. I'm not sure what's happening for you?

 

[Bob Barrows [MVP]]

My possible error. I received the following when running the
procedure in A97

Run-time error '3127':
The INSERT INTO statement contains the following unknown field name:
'p1'

Hmmm - so it didn't treat it as a parameter ... I wonder if A97 required the
PARAMETERS declaration ... I don't remember that being the case.

The VALUES clause might not have been supported in A97 ... I just don't
remember.
I will need to log into my work machine to try it out on A97 ... No, VALUES
was supported.

From A97 Online Help in the INSERT INTO topic:
Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

I just tried it and got the same error as you. Now to try a PARAMATERS
declaration ... no, that didn't help.

Ah! I needed to supply the column names in the INSERT INTO clause. This
works:

sql = "insert into calendar (CalendarDate,PolicyYear) " & _
"Values([p1],[p2])"

Run in A2003 it processes as advertised.

Values is not something I ever use and am a tad confused about your
usage. Have been searching A2003 Help File for explanation/example(s).

If you shed some light on the subject, it'd be most appreciated.

I'm not sure if you are confused about the parameters ([p1] and [p2]) or the
VALUES() clause.

The VALUES clause allows you to construct an INSERT statement without
selecting data from a table.

Is it the technique of passing values to the parameters that's causing the
confusion? Perhaps if I had been more explicit, like this:
qdf.Parameters(0) = d
qdf.Parameters(1) = Year(DateAdd("m", -9, d))

or like this:
qdf.Parameters("p1") = d
qdf.Parameters("p2") = Year(DateAdd("m", -9, d))

Does that help? I've gotten into the habit of referring to items in
collections via their ordinal position rather than their names. Sorry if
that led to mystification.