Retrieve different fields based on input?

[SimonIsCool]

I am writing a query to find costs in the years after a certain delivery date.

I have a table with fields "Delivery Date" and 1998-2005 Yearly Costs.

If the delivery date is after any of the 1998-2005 cost fields (such as a 2003 delivery date), the previous fields are left as 0 (in this case 1998-2003).

I need to write an field in a query that can retrieve costs 1 or 2 or 3 or etc years after the delivery date. So for a 2000 delivery date, the field "1 Year After" would retrieve the 2001 cost field from my table, the field "2 Years After" would retrieve the 2002 cost field from my table, and etc.

The problem is that my delivery dates vary, and I don't know how to add a set number of years to my dates and retrieve the corresponding field from the table.

I am currently using a bulky and inefficient switch statement foe each field:

Year 1: Switch(Year([Delivery])=1996,0,Year([Delivery])=1997,[Raw Data]![Act 1998 EUR],Year([Delivery])=1998,[Raw Data]![Act 1999 EUR],Year([Delivery])=1999,[Raw Data]![Act 2000 EUR],Year([Delivery])=2000,[Raw Data]![Act 2001 EUR],Year([Delivery])=2001,[Raw Data]![Act 2002 EUR],Year([Delivery])=2002,[Raw Data]![Act 2003 EUR],Year([Delivery])=2003,[Raw Data]![Act 2004 EUR],Year([Delivery])=2004,[Raw Data]![Act 2005 EUR],Year([Delivery])>=2005,0,Year([Delivery])<1996,0)

Year 2:Year 2: Switch(Year([Delivery])=1996,[Raw Data]![Act 1998 EUR],Year([Delivery])=1997,[Raw Data]![Act 1999 EUR],Year([Delivery])=1998,[Raw Data]![Act 2000 EUR],Year([Delivery])=1999,[Raw Data]![Act 2001 EUR],Year([Delivery])=2000,[Raw Data]![Act 2002 EUR],Year([Delivery])=2001,[Raw Data]![Act 2003 EUR],Year([Delivery])=2002,[Raw Data]![Act 2004 EUR],Year([Delivery])=2003,[Raw Data]![Act 2005 EUR],Year([Delivery])=2004,0,Year([Delivery])>=2005,0,Year([Delivery])<1996,0)

Etc up to year 5.

But this system is obviously not very efficient because i have to modify 5 switch statements every time a new year of data is entered into the system.

Is there any way to write a function that can do this for me in VB?

For example, a function in the format:

RetrieveCosts( [column with the delivery dates], # of years to add)

Where the function would return 0 if the column it tries to retrieve does not exist (such as retrieving year 5 costs for a 2005 delivery date).

[shukla]

why you don't use field index and year linking

[SimonIsCool]

heh... sorry im a noobie with vb.... so excuse my thickness.

How would you go about doing that?

[shukla]

what is structure of your database ?
And what code you are now using ?

[SimonIsCool]

I've been sticking to the expression builder and SQL in the queries, but ive done a little writing in ADO... i honestly dont know much (if anything) about VB though. Essentially the table i want to query is called "raw data" and the columns you can probably tell from the two switch statements i posted.

[shukla]

give me actual vb-code which you are now using.

[SimonIsCool]

i'm not using any vb code.. im using the switch statements i listed above in SQL. I don't know how to make VB do what I need it to, because i don't really know vb in general

[shukla]

use this code:
year1= year(delivery)
select case year1
case 1996:
'your code for year 1996

case 1997:
'your code for year 1997

case 1998:
'your code for year 1998

...
and so on

[SimonIsCool]

essentially that vb statement does the same thing that my switch statements do. I was wondering if there is a way to automate that in vb, so instead of a bunch of case statements, i could just add a certain number of years to a field name

Essentially... this is what i'm looking for:
1. substring the year out of the delivery date
2. add the inputted parameter amount (1-5) to the year value
3. take this year value and concat it into the string "Act [yearvalue] EUR"
4. Retrieve the field from the table that is titled "Act [yearvalue] EUR"


Essentially a 4 step function. But i don't know visual basic so i dont know the specific way of coding it.