473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Forward Reference Calculation Problem?

Hello

My MainForm manages Employee records. I have created another form that
manages employee promotions. On the MainForm a button launches the
PromotionForm and lists the promotions for the employee shown on the
MainForm. The buttons clickevent looks like this: DoCmd.OpenForm
stDocName, , , "EMPID = " & Me.txtEMPID

The results are FILTERED. The Form is Continuous.
Fields are: (EmpID) (Rank) (PromoDate) and UnboundTxtBox:
(TimeInGrade)
I have placed a UnBound TextBox in the PromotionsForm called
(TimeInGrade) and want to be able to display the amount of time the
employee held a rank before moving on to the next promotion. The Last
promotion to be calculated as (date() - LastPromo)

EXAMPLE:

RANK PromoDate TimeInGrade
FF 01/01/1990 365days
Lieut 01/01/1991 1460days
Capt 01/01/1995 4361days

How do I calc forward to the next field or iterate the filtered results
to get my solution?

Greg

Dec 11 '06 #1
3 1595
Create a query using your table.

Type an expression like this into a fresh column in the Field row:

NextPromoDate: (SELECT Min(PromoDate) AS MinOfPromoDate
FROM Table1 AS Dupe
WHERE (Dupe.EmpID = Table1.EmpID)
AND (Dupe.PromoDate Table1.PromoDate))
Replace "Table1" with the name of your table.

Once you have that working, you can use Nz() to supply today's date if
there's no future promotion, and use DateDiff() to get the difference:

TimeInGrade: DateDiff(d", [PromoDate],
Nz((SELECT Min(PromoDate) AS MinOfPromoDate
FROM Table1 AS Dupe
WHERE (Dupe.EmpID = Table1.EmpID)
AND (Dupe.PromoDate Table1.PromoDate)), Date()))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<Ap******@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...
Hello

My MainForm manages Employee records. I have created another form that
manages employee promotions. On the MainForm a button launches the
PromotionForm and lists the promotions for the employee shown on the
MainForm. The buttons clickevent looks like this: DoCmd.OpenForm
stDocName, , , "EMPID = " & Me.txtEMPID

The results are FILTERED. The Form is Continuous.
Fields are: (EmpID) (Rank) (PromoDate) and UnboundTxtBox:
(TimeInGrade)
I have placed a UnBound TextBox in the PromotionsForm called
(TimeInGrade) and want to be able to display the amount of time the
employee held a rank before moving on to the next promotion. The Last
promotion to be calculated as (date() - LastPromo)

EXAMPLE:

RANK PromoDate TimeInGrade
FF 01/01/1990 365days
Lieut 01/01/1991 1460days
Capt 01/01/1995 4361days

How do I calc forward to the next field or iterate the filtered results
to get my solution?

Greg

Dec 12 '06 #2
Thankyou Allen, that's a great idea and I will experiment with it.

Before your response, I have been pursuing the following:

RANK PromoDate TimeInGrade
FF 01/01/1990 365days
Lieut 01/01/1991 1460days
Capt 01/01/1995 4361days

How do I calc forward to the next field ???

In my subform I created a column called txtTimeInGrade and in the
ControlSource placed the function reference
=Days_DateDiff([PROMODATE],Date()) and it displays the result between
the promodate and todays date in the exact format I would like, however
the Date() parameter needs to be changed to a forward reference to the
next rows PromoDate to give me the difference between the promotion
dates. Something like:
=Days_DateDiff([PROMODATE],[PROMODATE].Row+1) but I am having
difficulty figuring out what the row reference syntax should be???
And then of course, if this works I will need to address the last
record issue attempting to forward reference a record that is not
there?

ThankYou
Greg

Dec 18 '06 #3
If you don't mind a read-only form (i.e. you don't need to edit the
records), you can use a subquery.

If you need it to be editable, you will need to use DLookup() in your form
instead. Unfortunately, DLookup() doesn't allow you to specify which record
you need to retrieve, so you will probbaly need to use this extended version
of the function called ELookup() instead:
http://allenbrowne.com/ser-42.html

More information about how to build queries to retrieve other values:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
or:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<Ap******@gmail.comwrote in message
news:11**********************@48g2000cwx.googlegro ups.com...
Thankyou Allen, that's a great idea and I will experiment with it.

Before your response, I have been pursuing the following:

RANK PromoDate TimeInGrade
FF 01/01/1990 365days
Lieut 01/01/1991 1460days
Capt 01/01/1995 4361days

How do I calc forward to the next field ???

In my subform I created a column called txtTimeInGrade and in the
ControlSource placed the function reference
=Days_DateDiff([PROMODATE],Date()) and it displays the result between
the promodate and todays date in the exact format I would like, however
the Date() parameter needs to be changed to a forward reference to the
next rows PromoDate to give me the difference between the promotion
dates. Something like:
=Days_DateDiff([PROMODATE],[PROMODATE].Row+1) but I am having
difficulty figuring out what the row reference syntax should be???
And then of course, if this works I will need to address the last
record issue attempting to forward reference a record that is not
there?

ThankYou
Greg

Dec 19 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ryan Mitchley | last post by:
Hi all I have the functions friend CComplexMatrixTemp eye(const size_t nN); and friend CComplexMatrixTemp & chol(CComplexMatrixTemp &z); I would like create expressions of the form...
5
by: Thomas Matthews | last post by:
Hi, I have a Display class. I would like to write a function that takes a range of objects and displays them. The range would be specified by two forward iterators: start and end (one past...
10
by: Alan Lee | last post by:
Hi i am writing a small simulation for a bunch of atoms jumping around on a surface. I know i am a crappy programmer since i am not very familiar with object oriented languages. I am woindering...
11
by: Randy Yates | last post by:
I'm having a problem with forward references. For example, class DATE; class MYCLASS; class MYCLASS { public:
6
by: Markus Dehmann | last post by:
I have a circular dependency between two classes. The FAQ hint about a forward declaration does not help in my case ( How can I create two classes that both know about each other?) Error:...
4
by: Michiel Alsters | last post by:
Hello everybody, I hope anybody can help me. I'll try to give a brief overview of my problem. I have running a program that performs a heavy calculation. To give the user feedback what the...
7
by: Michael B Allen | last post by:
I have a forward reference like: struct foo; int some_fn(struct foo *param); Because the parameter is a pointer the compiler is satisfied. But now I wan to change 'struct foo' to a...
8
by: Mohammad Omer Nasir | last post by:
Hi, i made a structure in header file "commonstructs.h" is: typedef struct A { int i; A( ) {
6
by: barcaroller | last post by:
Why does the following code cause a compiler error? class A; // forward reference class B { foo() { a = new A;
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.