(2) Date Instances that I need help with!

[richierich]

First what I need help with is in a report that I have that I have hauled multiple truck loads with a particular truck on a particular day and I want on the report to show me on the footer of the report how many days each truck worked. I can get the total number of loads each truck hauled for that particular time frame in my report with the count functin but when it comes to disreguarding the multiple records on that particular date icantmakethat happen.

The other thing that I need is that I would like to be able to create myself a hotkey for either a consecutive date or possibly even a consecutive ticket number formy forms. When I use one of my forms for timecard entry I would like tobe able to just press a function key andbring up the next consecutive date finding that date off the last date entered. I have searched your forum for help with either of these date functions with no luck.

Your help willbe greatly appricated.
Rich

[ADezii]

First what I need help with is in a report that I have that I have hauled multiple truck loads with a particular truck on a particular day and I want on the report to show me on the footer of the report how many days each truck worked. I can get the total number of loads each truck hauled for that particular time frame in my report with the count functin but when it comes to disreguarding the multiple records on that particular date icantmakethat happen.

The other thing that I need is that I would like to be able to create myself a hotkey for either a consecutive date or possibly even a consecutive ticket number formy forms. When I use one of my forms for timecard entry I would like tobe able to just press a function key andbring up the next consecutive date finding that date off the last date entered. I have searched your forum for help with either of these date functions with no luck.

Your help willbe greatly appricated.
Rich

I'm a little hazy on the first part, and would like a little more detail and actual data, but I can help you on part 2:

'Declare a Public Variable

Public MyControl As Control

'Create a Public Function Procedure

Public Function fInsertConsecutiveDate()
On Error GoTo Err_fInsertConsecutiveDate
 
  MyControl = DLast("[Your Date]", "Your Table") + 1
 
Err_fInsertConsecutiveDate
  Exit Function

'On the GotFocus Event of any Control on which you want this Hot Key functionality, set the 'complete reference' to the Control. We will use [txtTest] on frmTest as an example

Private Sub txtTest_GotFocus()
  Set MyControl = Forms!frmTest![txtTest]
End Sub

Create an AutoKeys Macro
__Macro Name - +{F10}
__Action - RunCode
__Function Name - fInsertConsecutiveDate ()

Now, when the Focus is on [txtTest] and you press SHIFT+F10, the next consecutive Date after the last Date entered will be placed in the Text Box. If you are not interested in a Hot Key, you can assign = DLast("[Your Date]", "Your Table") + 1 to the Default Value of any Text Field for which you want this functionality. When you add a New Record, the next consecutive Date after the last Date will automatically be entered into this Field.

[richierich]

The first of my question has to do with my report. I will try to be more clear.
What I would like to have is the amount of days that the truck worked. I have data in my reports stating that truck a hauled 3 loads all with the same date. Driver of truck a hauled all these loads and I need to know how many days per reporting period each driver and truck worked. So then what I can do is figure out an average for the reporting period on what the driver as well as the truck made per day. I can take care of the average but just need help figuring out how to actually count days vs. counting records or entries.

Thanks
Rich

[ADezii]

The first of my question has to do with my report. I will try to be more clear.
What I would like to have is the amount of days that the truck worked. I have data in my reports stating that truck a hauled 3 loads all with the same date. Driver of truck a hauled all these loads and I need to know how many days per reporting period each driver and truck worked. So then what I can do is figure out an average for the reporting period on what the driver as well as the truck made per day. I can take care of the average but just need help figuring out how to actually count days vs. counting records or entries.

Thanks
Rich

__1 Create a Query with only 2 Fields: [Driver] and [Date] (qryDriverAndDate).
__2 Set Criteria for the Date Range within the [Date] Field.
__3 Set the Unique Values Property to Yes. This will give you Unique values for each Driver and Date combination (If Ted made 6 runs on 12/12/2006, he would only show up once in the Query).
__4 Run the following code to determine how many days per Reporting Period each Driver worked.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
 
'Get a 'Unique' lsit of all Drivers, use your Table Name.
Set MyRS = MyDB.OpenRecordset("SELECT DISTINCT [Driver] From tblTruck", dbOpenDynaset)
 
Do While Not MyRS.EOF
  Debug.Print MyRS![Driver] & " worked " & DCount("[Driver]", "qryDriverAndTruck", "[Driver]='" & MyRS![Driver] & "'") & _
              " day(s) this period"
  MyRS.MoveNext
Loop
 
MyRS.Close

Typical Output would be:

Frank worked 1 day(s) this period
George worked 1 day(s) this period
Lou worked 2 day(s) this period
Steve worked 1 day(s) this period
Steven worked 1 day(s) this period
Ted worked 4 day(s) this period
Tom worked 2 day(s) this period

NOTE: For determining how many days each truck was utilized per period, make appropriate substitutions. I strongly feel that one of the other Experts will come up with a better solution, but for now this is all I have to offer.

[NeoPa]

If doing this in a query I would advise having a subquery (Subqueries in SQL) which groups at the first level with a sum() field for the item. In the outer query you can use Count() to get the number of days used and Sum() to get the overall sum.
In a report, use Sorting and Grouping to do something similar. You will need group footers to Sum() and Count() the items.