Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 22nd, 2009, 12:50 AM
Registered User
 
Join Date: Jan 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calculation using previously entered data in a form

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
 
Old July 22nd, 2009, 10:19 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I don't understand what you are asking.
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old July 22nd, 2009, 02:48 PM
Registered User
 
Join Date: Jan 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 22nd, 2009, 07:17 PM
Registered User
 
Join Date: Jan 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Last edited by wobmat; July 23rd, 2009 at 04:30 AM.. Reason: Changed method of getting value





Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW TO capture a previously loded form in MDI appl Deepa7476 Visual Basic 2005 Basics 0 May 4th, 2006 05:48 AM
Re: need to convert data entered to all caps flyfish Access 5 March 18th, 2005 12:35 PM
retaining the last data that was entered Calibus Classic ASP Databases 4 October 14th, 2004 03:31 PM
How to check data entered 3 textboxs minhpx General .NET 1 September 13th, 2004 11:46 PM
Maintain entered data sporkman43 Classic ASP Basics 2 October 14th, 2003 02:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.