Autofilling a date/time field

[wrweaver]

In the database I'm working on, the user has a daily log on a continuous form that encompasses some routine activities. For example at 6am every morning they report to work and at 4 4pm every evening they check out; at noon they eat lunch etc. I want to have a button that autopopulates the date time field with the current date and those specific times so they don't have to enter the routine stuff daily to save time. The field is "StartTime." It's a date/time field value in the table and the property sheet format is mm/dd/yy hhnn. I began the code as

Private Sub MorningRoutine_Click()
 
DoCmd.GoToRecord,,acNewRec
Description = "Report to work"
StartTime = #" & Date & 0600"#"
DoCmd.GoToRecord,,acNewRec
Description = "Lunch break"
StartTime =   #" & Date & 1200"#"
DoCmd.GoToRecord,,acNewRec
Description = "Cleanup and leave work"
StartTime =   #" & Date & 1600"#"
 
End Sub

But I keep getting the error "The value you entered isn't valid for this field."

Any help is appreciated! Thanks for having a look!

 

[pr2-eugin]

It is because the Date value you entered is not valid.. Dates in Access are a bit tricky.. Format is only a display.. The actual values are stored as Double values.. So store the Now() value and in the reports/Queries apply the format..

Apart from the solution, some things to note here.. First you have not concatenated the 0600, 1200, and 1600 as string to be joined, even if you did, if you simply Print the values in the Immediate window, this is what you would get..

? Date & "0600"
25/04/20130600

Even if you try something like this..

? Format(Date, "mm/dd/yy") & " 0600"
04/25/13 0600

It will still not be a valid Date.. So as mentioned, store the Now value, and set format in Query..

Totally out of Interest, if the user clicks one button that will stamp his Start, break and End times.. Then what would you do if someone finishes early? If the user comes in and then goes home during lunch break? Will he/she be still be recorded as full day attendance?

 

[wrweaver]

They don't use the log until the end of the day when they fill it out and submit it. The times are the same every day. There is no variation. They can't use the now() value because that would give them a time of about 3:45 pm when they are filling out the time sheet. Does that make sense?

 

[pr2-eugin]

Try using this..

? CDate(Date & " " & TimeSerial([COLOR=Red]16[/COLOR],0,0))
25/04/2013 16:00:00

Change the highlighted portion as per your need.. Since we use the CDate function, the return value is a proper Date, you do not need to surround them with #

 

[wrweaver]

Worked perfectly!!! Thanks a million times!

 

[pr2-eugin]

Glad to help.. Good luck..