Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: 3rd shift help

  1. #1
    Join Date
    May 2013
    Posts
    40

    Unanswered: 3rd shift help

    Good morning,

    I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime)… the problem is that it’s not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.

    Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]

    Any help you can offer so that the users will be able to request a report based on a start and end date along with a start time and end time I would really appreciate it.

    Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM – 4/15/2013 10PM

    Your help is greatly appreciated

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    A suggestion. You can store both date and time in the same field and it would simplify your database. Then, if you want to just view the date or just the time use formatting. I believe that all date date types store both the date and time and if one or the other is not entered it defaults, i.e, your time field may be storing the date 01/01/1900 along with your time and if that is so this could explain why you don't see anything.

  3. #3
    Join Date
    May 2013
    Posts
    40

    3rd Shift Help

    Thanks for the reply billmeye,

    Forgive my lack of knowledge...

    Originally I tried to run the query from a single field called “StartTime” (this field does have both date and time) using the same criteria (StartDate and EndDate) but still I got no success.

    When I ran my report using date range only [StartDate] and [EndDate] it wouldn’t pull any data after midnight for the 3rd shift employees. It only showed information for 1 day.

    Example
    StartDate = 4/14/13
    EndDate = 4/15/13

    3rd shift starts 10pm 4/14/13
    3rd shift ends 6:30AM 4/15/13

    I’m only getting the 2hrs of totals for the 14th and missing everything from the 15th.

    I hope this make since and I again appreciate your help.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    If you want to, post your DB and I can better see what is happening.

  5. #5
    Join Date
    May 2013
    Posts
    40
    Thanks,

    You may have to hold Shift when opening…
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    I believe it was a matter of formatting. In your table you set format to short date, in your form again short date and then your report short date. I changed all of them to general date which also includes time and believe the filtering will work. You will need to enter your start and end with time:

    Start: 4/28/2013 11:00:00 PM
    End: 4/29/2013 8:00:00 AM

    Incidentally, I noticed Gunther. Would that be Gunther International in Norwich, CT?
    Attached Files Attached Files

  7. #7
    Join Date
    May 2013
    Posts
    40
    That Worked!!!!!

    you're awesome and I really apprecite you taking the time to help me.


  8. #8
    Join Date
    Jan 2005
    Posts
    146
    I'm glad it worked.

    Also, I noticed Gunther. Would that be Gunther International in Norwich, CT?

  9. #9
    Join Date
    May 2013
    Posts
    40
    sorry about that... yes it's Gunther Int.

    thanks, again.

  10. #10
    Join Date
    Jan 2005
    Posts
    146
    I work at Argo Transdata, we supply some PCB assemblies for you guys. Small world.

  11. #11
    Join Date
    May 2013
    Posts
    40

    Cool

    That's awesome! ...a small word indeed.

    have a great rest of the day.

  12. #12
    Join Date
    May 2013
    Posts
    40
    do I have to start a new thred or can I continue with this one? please let me know and I'll re-post.

    my question is how can I get time to sum when it's over 24hours?

    thanks again you're awesome!

  13. #13
    Join Date
    Jan 2005
    Posts
    146
    Not knowing what your adding, the DateDiff function will yield results:

    DateDiff("h",#06/12/2013 7:00 AM#,Now()) = number of hours
    DateDiff("n",#06/12/2013 7:00 AM#,Now()) = number of minutes
    DateDiff("d",#06/12/2013 7:00 AM#,Now()) = number of days

    If that is what you mean. You can use variables for the begin and ending times.

  14. #14
    Join Date
    May 2013
    Posts
    40
    Thanks again for your help,

    I’m needing to add Active Time + Idle Time = Total Time

    Daily this works fine but when I do something with a date range over 24hrs it doesn’t work correctly. The fix you suggested, where would I apply that?
    This is the same database as mentioned before with needed to date range query across 3rd shift .

    Thanks again!

  15. #15
    Join Date
    May 2013
    Posts
    40
    I can re-attached the Database if needed.

    thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •