query with dynamic field selection

[StellaIndigo]

Hi

I have a db with 250,000+ stock records. Each record has amoung other fields the usage for each month, jan, feb .. dec.

If I ran the report at any time I want to sum the last 3 months. So, I run the report in Apr, the sum is (jan + feb + mar), if I run it in May it's (feb + mar + apr).

I thought I might be able to write a quick VBA function but I don't know how to reference the current record from the query within the module.

Anyone got any ideas or alternative solutions?

thanks in advance

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[MakeItSo]

You could implement a DateDiff criteria in your query.
Check Access help file on "DateDiff" and limit your datestamp field with it.

Hope this helps,
Andy


[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[lupins46]

If each record has a unique id then in your query you have an extra column which is
MySum:GetTotal(recordid)

Assuming the month totals start at field position 5 (where first field is posn 0), then the function will be something like this:

Public FunctionGetTotal(vID)
dim strsql, curmth, x,y,z
dim rs a dao.recordset

strsql = "Select * from mytable where [recid] = " & vId
curmth = month(date())
set rs = currentdb.openrecordset(strsql)
x =0
y=0
z =0
Select case curmth

Case is >2
x = rs(curmth + 2)
y= rs(curmth+3)
z = rs(Curmth+4)

Case 1
z =rs(5)

case 2
y = rs(5)
z = rs(6)

end select

GetTotal = x+y+z

set rs = nothing
end function

 

[StellaIndigo]

Thanks Lupin46

This is what I have done before but it always seemed a waste after the record had been read in the initial select statement I then have to open the table, reselect the correct record and perform my math.

It would be nice to be able to pass the entire record to a function for processing, i.e MySum( rcdSelectedRecordID ).

In the datawarehouse side of this application I will need to perform the same type of query but the result maybe obtianed from 2 archive tables (1 for each year). So running in Jan this year the Sum = Usage2004!Jan + Usage2003!Dec + Usage2002!Nov.

(

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[lupins46]

Well you can pass the whole twelve monthly values into a function as arguments and it will work out the result but it would be a rather long call statement!