Hi All,
Long time reader, first time poster.

I need to look up a value in the table that the form is based in.

Table contains Monthly KPI data for Staffing Levels.
Each month is dated as the last day in the month (i.e. 30/06/2009 dd/mm/yyyy)
I need to find the corresponding value in the data for 30/06/2008

I have tried DLOOKUP but have failed, will keep trying in the meantime.

Table = tblKioskValues

The form is based on the query qryKioskValues (so as the sorting is logical) Everything that is in the table is in the query.
Form = frmKioskValues
Date = datMonth
FTE data = dblFTEPaidOut

I will move last years value into dblLastYearFTEPaidOut in the sub and will be used in a calculation to work out the percentage difference from last year. e.g. ([dblFTEPaidOut]-[dblLastYearFTEPaidOut ])/[dblFTEPaidOut]


Cheers in advance

I don't understand what you are asking.

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Hi mmcdonal,
I am entering data for last months stats.
datMonth = 30/06/2009
dblFTEPaidOut = 27.8

I want to use the value for dblFTEPaidOut in a previous record where:
datMonth = 30/06/2008
dblFTEPaidOut = 25.7
And then I can calculate that there was a 7.5% increase.

Cheers

Now have it working.
I created a query that shows the record from the previous year
(datMonth=DateAdd("m",-12,[forms]![frmKioskValues].[datMonth])

VBA opens the form based on the query.

I then use dblLastYearFTEPaidOut = Forms!frmCalculateKioskPercentages.dblEffectivePai dFTE.Value

Close the form.

Then calculate the percentage difference. dblPercentageFTEPaidOut = (dblEffectivePaidFTE - dblLastYearFTEPaidOut) / dblEffectivePaidFTE

Glen