Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: Access 2010 date AND time selector

    I've been searching around online and in these forums, but have not yet found a solution to this quesiton. The closest thing that I've found is the following link:
    Access Help and How-to - Microsoft Office Time Picker; Date Picker; Date Time Picker - UtterAccess Discussion Forums, but it does not work in Access 2010.

    I am looking to create a form that allows for a date and time selection window that would become the selection criteria for a set of queries using a general date format "MM/DD/YYYY HH:MMS AM". My reason for this is to keep production data from my 3rd shift, which starts at 10:30pm, continuous.

    What I would like to be able to do is have a pair of date/time pickers that would allow the selection from "1/10/2012 10:00:00PM" to "1/11/2012 3:00:00PM" to cover a single days 3rd and 1st shift production.

    I have thought about separating the date/time into two separate fields and running separate date/time selection boxes for the start/finish, but i feel that this would lead to potential reporting errors.

    Another option that would be very nice would allow the user to select a given date, say 1/14/12 and have the application automatically set the date range from 10:00:00 PM on 1/13/12 to 10:00:00PM on 1/14/12...so if this is an option i'm all ears.

    any help is much appreicated.

    Thanks

    HC

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd get the user to specify the date and using a radio button the shift ID
    1, say 10:00
    2 15:00
    3 22:00

    or, better yet
    option one is 10, two is 15, 3 is 22 (so you immediately have the hours for the shift based on the value of the selected option)

    When the user specifies the date take the value from the radio group and add it to the date. keep the date display to be just the date (IE alter the format). use the date control as the bound control

    you'd need set the correct radio option in the forms on current event
    you'd need to place some code behind the radio button on click event

    for the UI declare a frame and specify the 3 values, use a radio button as the design motif
    ..or better yet declare a table with those values as you never EVER know if someone is going to tinker with shift patterns

    in the forms on current event (this sets the radio button to the correct value)
    Code:
    if isdate(MyDateColumn) then 'if the mydatecolumn holds a valid date value then extract the time, otherwise set the time to be 10:00:00
      myframe.value = hour(MyDateColumn) 'assumign you trust your users
      'I never trust 'em so I'd probably use
      select case Hour MyDateColumn
      case 10,15,22: MyFrame.value = hour(MyDateColumn)
      default: MyFrame.Value = 10
      end select
    else 'we dont have a valid date so set the default
      MyFrame.Value = 10
      'consider adding a default date here
      eg MyDateControl.value = DATE() + 10/24' assuming 10:00:00 is your default
    endif
    'add any other set up code here
    if the frame on click event
    MyDateControl.value = clng(MyDateControl) + MyFrame.Value/24
    'use clng to convert the current date to integer (chop off any time element), then add back the value of the frame divided by 24 to convert from hours to proportion of a day

    make certain mydatecontrol doesn't display the time element, use a date picker to set that value
    Last edited by healdem; 01-14-13 at 12:19.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Hey,

    Thanks for the help, that sparked an idea that allowed someone with my basic...very basic....access skills to get this to work for our situation. We don't really need full date control as we generally only look back for the previous day's production and do not as a full reporting tool. Mainly used to give foreman immediate production feedback so that they can make adjustments as necessary.

    I tried coding everything, and it worked well within the coding (both dates show accuratley when you step through the code), but with my lack of Access/Coding skills I could not find a way to get the values for begin_date & end_date out to the queries that need to be constrained. Any suggestions to make these values visible to the queries would be much appreciated. The "Date_Selected" below is pulled from the calendar function in the form that contains the button.


    Public Sub Command3_Click()

    Dim Begin_Date As Date
    Dim End_Date As Date

    Begin_Date = (CLng(Associate_Productivity_Date_Selected) - 1) + 20 / 24
    End_Date = CLng(Associate_Productivity_Date_Selected) + 17 / 24
    DoCmd****nMacro ("Associate Productivity Macro")

    End Sub



    In the short term I ended up doing the coding for this right in the criteria of the queries....but i know this needs to be fixed. In the end the values for 20 & 17 need to be pulling the data directly from a table to make it easy to update as well.

    Thanks for the help.

    HC
    Last edited by HC1981; 01-14-13 at 18:52.

Posting Permissions

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