Date/Time help (1 Viewer)

[Vergy39]

I have a table of data that has a column that uses Date/Time of when received. This table is in a SQL database. I have linked the table to an access database so an associate without sql skills can pull reports. Problem is the form to pull the report requires to enter the begin date and time and the end date and time. The begin time is always going to be 12:01am and the end time is always going to be 11:59pm. Can I create a text box that allows us to enter the begin date and end date, but enters the times automatically? I have tried using input mask and the format __/__/____ 00:01:000 and __/__/____ 23:59:000, but with no success. Any assistance is greatly appreciated.

Thanks
David V

 

[VilaRestal]

Why not set the BeginDate and EndDate text boxes not include the time and then on each AfterUpdate event apend the relevant time to the field.
Useful functions are DateValue and TimeValue

You could have:

Private Sub BeginDate_AfterUpdate()
    Me!BeginDateTime = DateValue(BeginDate.Value) + TimeValue("00:01")
End Sub
 
Private Sub EndDate_AfterUpdate()
    Me!EndDateTime = DateValue(EndDate.Value) + TimeValue("23:59")
End Sub

You could make the textboxes unbound and then do something like:

Private Sub Form_Current()
    BeginDate.Value = FormatDateTime(Me!BeginDateTime, vbShortDate)
    EndDate.Value = FormatDateTime(Me!EndDateTime, vbShortDate)
End Sub

You'd also want some IsDate() validation in the BeforeUpdate events and you could also check the end date isn't earlier than the begin date there

 

[Vergy39]

Thanks VilaRestal, I tried this real fast, but could not get it to work. I am heading home now, but will try agian tomorrow. The other thing I wanted to mention was that I am using a Date picker so the associate can select the begin date and end date. The form is call frmDatePicker. The field names are _cmbDateStart and _cmbDateEnd. These tie to the query that pulls the data.

Anyway, will try again tomorrow.

Thanks again,
David V