468,738 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,738 developers. It's quick & easy.

(2) Date Instances that I need help with!

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
Feb 3 '07 #1
4 1472
ADezii
8,800 Expert 8TB
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
Expand|Select|Wrap|Line Numbers
  1. Public MyControl As Control
'Create a Public Function Procedure
Expand|Select|Wrap|Line Numbers
  1. Public Function fInsertConsecutiveDate()
  2. On Error GoTo Err_fInsertConsecutiveDate
  3.  
  4.   MyControl = DLast("[Your Date]", "Your Table") + 1
  5.  
  6. Err_fInsertConsecutiveDate
  7.   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
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTest_GotFocus()
  2.   Set MyControl = Forms!frmTest![txtTest]
  3. 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.
Feb 3 '07 #2
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
Feb 3 '07 #3
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2.  
  3. Set MyDB = CurrentDb()
  4.  
  5. 'Get a 'Unique' lsit of all Drivers, use your Table Name.
  6. Set MyRS = MyDB.OpenRecordset("SELECT DISTINCT [Driver] From tblTruck", dbOpenDynaset)
  7.  
  8. Do While Not MyRS.EOF
  9.   Debug.Print MyRS![Driver] & " worked " & DCount("[Driver]", "qryDriverAndTruck", "[Driver]='" & MyRS![Driver] & "'") & _
  10.               " day(s) this period"
  11.   MyRS.MoveNext
  12. Loop
  13.  
  14. MyRS.Close
Typical Output would be:
Expand|Select|Wrap|Line Numbers
  1. Frank worked 1 day(s) this period
  2. George worked 1 day(s) this period
  3. Lou worked 2 day(s) this period
  4. Steve worked 1 day(s) this period
  5. Steven worked 1 day(s) this period
  6. Ted worked 4 day(s) this period
  7. 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.
Feb 4 '07 #4
NeoPa
32,129 Expert Mod 16PB
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.
Feb 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Gerrit Holl | last post: by
1 post views Thread by Robert Mark Bram | last post: by
11 posts views Thread by lduperval | last post: by
6 posts views Thread by kevin carter | last post: by
7 posts views Thread by Jerome | last post: by
3 posts views Thread by Jon Paal | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
xarzu
2 posts views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.