Calculating #days between 2 records same field

[PMrider]

I'm building a database for deliveries. I want to be able to calculate the number of days between deliveries using just the [DateOfDelivery] field. Each delivery is a separate record on the form.

 

[Alexandre]

First you have to select the Delivery dates from the records you want to work with. Dlookup should do for that.

Then use the DateDiff function (see Access help for the syntax).

Alex

[This message has been edited by Alexandre (edited 02-22-2002).]

 

[PMrider]

This is what I came up with and it doesn't work. I'm not really understanding how do I do a lookup on the next or previous records to put in the current record so I can use the DateDiff
=DLookUp("[DateOfDelivery]","F_Delivery Subform","[DateOfDelivery] = Forms!F_Delivery Subform")


Pleae help....I've been trying to get this to do this for weeks now and today I finally came here....thanks

 

[Rich]

Look up the PrevRecVal function I'm sure it's what your looking for.
HTH

 

[PMrider]

I tried to lookup PrevRecVal in my help screens and could not find it anywhere. I have Access 2000. Is it somewhere else?

 

[David R]

Try this topic for a discussion of PrevRecVal (I can't find it in Acc2k either) http://www.access-programmers.co.uk/ubb/Forum7/HTML/002586.html

You also might consider using FindPrevious with a recordset clone.

HTH,
David R

 

[PMrider]

I'm very new to VB and i'm not sure how to do the recordsetclone or the FindNext. I'm wanting to use an unbound textbox to display the next record's DateOfDelivery on the current record. since my underlying query has my records sorting decending so I can show the most current delivery first. I've never used recordsetclone or FindNext. If anyone can give me an example of these and how to attached it to the unbound textbox would be greatly appreciated. If anyone wants me to email them the program so you can see what i'm talking about or want to have a crack at it will also be greatly appreciated. this is my last thing I have to do to this program to make it complete......THANKS to everyone for the help....

Frustrated.
Alan

 

[David R]

On further examination, the code in the topic I posted above IS the use of recordsets and FindPrevious to calculate PrevRecVal. Read there and see if you can follow what it does. I was going to recreate it but there seems no point in reinventing the wheel (though I'm still looking for the help file, if this is supposed to be a standard Access function).

David R

 

[David R]

Here's the article that explains PrevRecVal (Which I keep trying to type as FindPrevCal for some reason): http://support.microsoft.com/default.aspx?scid=kb;en-us;Q101081
It also has some lower-intensity solutions using Dlookup, if your database is amicable to them.

Good luck,
David R