01-14-13, 11:17 #1Registered User
- Join Date
- Jan 2013
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.
01-14-13, 11:40 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
personally I'd get the user to specify the date and using a radio button the shift ID
1, say 10: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)
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
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
01-14-13, 18:46 #3Registered User
- Join Date
- Jan 2013
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")
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.
Last edited by HC1981; 01-14-13 at 18:52.