Forward Reference Calculation Problem?

[ApexData]

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

[Allen Browne]

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.PromoDat e))
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.PromoDat e)), 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.goog legroups.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

[ApexData]

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

[Allen Browne]

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.goog legroups.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