ms access date and time. Time must be 7:00 AM and user must not edit time.

Question

I have a form where users input information for a test run on a particular date. This creates a table that i then link with another table based on the date and an ID. In this other table (filled by a form created by someone else) all of the times are 7:00 AM. Whoever created the form for that table did what I'm now asking about; since the dates must match EXACTLY in order for the tables to be joined, i need to do this, too. The way I want to do this is to have two fields in my form. One for the date (mm/dd/yyyy) and one for the time (##:##:## xM) and i want the time field to be uneditable by the user. This way the user knows that he/she cannot edit the time. So far i know how to do all this with input masks and such. However, I don't know how to add these two fields together and then store that in the table. Help?

Answer 1

I would write an update to set all the existing times to midnight (i.e. 00:00:00), and then alter the form so that it also truncates dates to midnight. The add a Validation Rule or CHECK constraint to ensure the values cannot be anything other than midnight. Then, if you are using user-level security, review the table privileges to ensure users cannot remove the Validation Rule or CHECK constraint.

Answer 2

User a datetime picker control - let the user just pick the date portion. The time portion sits in another control - a Label or a locked Textbox.

When you write your SQL to update / insert into your table, you can simply add these two control 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 as

DoCmd.ExecuteSQL ('Insert Into MyTable (Col1, DateTimeColumn) Values (1, #' & MyDatetimeValue & '#')

Answer 3

It sounds to me like you have a single Date/Time field in your form's record source, and you want to display the date portion in one form 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 your form which both use the same Date/Time field as their control source. Set the Format property of txtDate to Short Date. Set the Format property of txtTime to Medium 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 = DateValue(Me.txtDate) + #7:00:00 AM#
End Sub

If you need to massage existing values in your table, you can use an update "query":

UPDATE YourTable SET YourDateField = DateValue(YourDateField) + #07:00#
WHERE YourDateField IS NOT NULL;