Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 22nd, 2009, 01:50 AM
Registered User
 
Join Date: Jan 2008
Location: Auckland, , New Zealand.
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
Reply With Quote
  #2 (permalink)  
Old July 22nd, 2009, 11:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old July 22nd, 2009, 03:48 PM
Registered User
 
Join Date: Jan 2008
Location: Auckland, , New Zealand.
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
Reply With Quote
  #4 (permalink)  
Old July 22nd, 2009, 08:17 PM
Registered User
 
Join Date: Jan 2008
Location: Auckland, , New Zealand.
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 05:30 AM. Reason: Changed method of getting value
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 06: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 04:31 PM
How to check data entered 3 textboxs minhpx General .NET 1 September 14th, 2004 12:46 AM
Maintain entered data sporkman43 Classic ASP Basics 2 October 14th, 2003 03:11 AM



All times are GMT -4. The time now is 11:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.