Ms access to date and time. The time must be 7:00 and the user must not edit the time
I have a form where users enter information for a trial run on a specific date. This creates a table, which is then linked to another table based on the date and ID. In this other spreadsheet (filled with a form created by someone else), the time is 7:00. Whoever created the form for this table did what I am now asking about; since the dates have to match EXACTLY for the tables to be merged, I need to do this too. The way I want to do this is to have two fields in my form. One for date (mm / dd / yyyy) and one for time (##: ##: ## xM) and I want the time field to be invalid user. Thus, the user knows that he cannot change the time. So far I know how to do all this with input masks, etc. However, I don't know how to add these two fields together,and then store them in a table. Help?
source to share
You seem to have one Date / Time field in your form's record source and you want to display the date portion in one control and the time portion in another control. And the time portion should always be 7:00 am.
Add two text box controls (txtDate and txtTime) to the form that use the same Date / Time field as the control source. Set the property Format
to txtDate for a short date. Set the Format
txtTime property to average time. Also, on the Data tab for txtTime, set Enabled to No and Locked to Yes.
Then, in the After Update event of txtDate, you can use:
Private Sub txtDate_AfterUpdate() Me.txtDate.Value = DateValue(Me.txtDate.Value) + #7 AM# End Sub
If you need to bulk the existing values in a table, you can use the query UPDATE
:
UPDATE YourTable SET YourDateField = DateValue(YourDateField) + #07:00#
WHERE YourDateField IS NOT NULL;
source to share
I would write an update to set all existing times to midnight (i.e. 00:00:00) and then change the shape so that it also truncates the dates to midnight. Add a validation rule or CHECK
to ensure that the values cannot be anything other than midnight. Then, if you are using user-level security, review the table privileges so that users cannot remove the validation rule or constraint CHECK
.
source to share
Time Picker Custom Control - Allows the user to simply pick a date. The temporary part is in another control — a shortcut or locked text box.
When you write your SQL to update / insert to your table, you can simply add these two watchdog values together as
Declare MyDateTimeValue as String
'This creates value of MM/DD/YYYY
MyDateTimeValue = MyDateTimePickerControl.Value
'This adds a blankspace and HH:MM:SS to become "MM/DD/YYYY HH:MM:SS"
MyDateTimeValue = MyDateTimeValue + ' ' + MyTimeLabelControl.Value
Use the variable MyDateTimevalue in your SQL statement like
DoCmd.ExecuteSQL ('Insert Into MyTable (Col1, DateTimeColumn) Values (1, #' & MyDatetimeValue & '#')
source to share