Calculating #days between 2 records 1 field

[PMrider]

ok, Here is my problem. I want to be able to calculate the number of days between
deliveries on my subform.

The fields are:
InvoiceID, Winter/Summer, DateOfDelivery, GallonsDelivered, PriceperGallon,
RateStatus, Driver, Remarks.

The final result i'm trying to achieve is to have a text box that will calculate the [daily
avg usage rate] based on the DateDiff of the current record [DateOfDelivery] and the
previous record [DateOfDelivery] which will give me #Days between deliveries. Then I
will divide GallonsDelivered by #Days in a different text box for the [Daily Avg Usage Rate].
My problem is I dont know how to use recordset clone or FindPrevious VBA. I looked in help but i'm not understanding how to do it. Any help would be appreciated. Also If by chance anyone wants to take on the challange of doing it, I can email the program to you so you can take a look at it....Thanks

PMrider

 

[tunsarod]

Add a box to your form and in its ControlSource property type everything in blue.


=IIf([RecNum]>1,[GallonsDelivered]/([DeliveryDate]-DLookUp("DeliveryDate","tblDeliveries","[RecNum] = ([txtRecNum]-1)&quot),Null)


This requires that the table has a field called RecNum which is an Autonumber sequenced type.

Also to avoid ambiguity I have used txtRecNum as the name for the RecNum field on the form.

I assume you or your client are the users of the fuel in which case all records refer to your fuel supplies. If you were the supplier and records were held of many different clients then working out the useage for each client would involve a more complex approach in which records would have to be selected by clientID and delivery date, possiblly using a recordset clone, but this way is pretty simple.

Good luck,
Rod

 

[PMrider]

This is an LP database that tracks deliveries to customers and it also figures out when the next delivery date will be. I got that part down though. The clients are on one table and the deliveries are on another with a one-to-many relationship. the form with the deliveries is a subform named [F_Delivery Subform] the main form is [F_Name] the subform has a [NameID] for linking and also has [InvoiceID] for each delivery. The subform is based on a query so I can sort [DateOfDelivery] Desending so that the most current record is on top. table names are "T_Name" and "T_Delivery"

The code below produced an #ERROR. I filled in the right field names but it doesn't seem to work. Any advice?


=IIf([InvoiceID]>1,[GallonsDelivered]/([DateOfDelivery]-DLookUp("DateOfDelivery","T_Delivery","[InvoiceID] = ([txtInvoiceID]-1)&quot),Null)

 

[tunsarod]

I have replicated your forms and tables and a working solution follows:

In the subform add two fields: txtPrevDelivery and txtGallsPerDay

The Control Source for txtPrevDelivery is:

=DMax(&quot;DateOfDelivery&quot;,&quot;T_Delivery&quot;,&quot;[DateOfDelivery] < [txtDateOfDelivery] AND [NameID] = [txtNameID]&quot


As you can see I have added 'txt' to the field names so as to avoid ambiguity in the formulae.

The control source for txtGalsPerDay is:

=IIf(IsNull([txtPrevDelivery]),Null,[GallonsDelivered]/([DateOfDelivery]-[txtPrevDelivery]))


You could rename the subform fields 'Invoice', 'Account', 'Del Date', 'Gallons' and 'DayGals' which will create better looking column labels for the subform but remember to use these names in the formulae.

If you can't get it working post your email and I'll send you a copy of the model I made.

Rod

 

[PMrider]

Rod....
I tried the code in an unbound text box in the Control Source on the subform and I also tried to put it in the Query SQL and they both got #Error. I feel like i'm sooooooo close to solving this but I think something might be wrong with my subform...here is my email.

 

[PMrider]

Sorry, email is akolln@srt.com

 

[tunsarod]

Model Db sent - good luck.
Rod