Date/Time Simplify (1 Viewer)

[rangersedge]

I have a form that has a date worked at the top and then an area to enter times in and out. The times in and out use the default Date/Time field and then calculate the hours worked. How can I have the times in and out read the date worked field for the date section and only require me to enter the times?

 

[burrina]

Assuming your date field is set to today's date by default, do you not want to enter any more time if it's not that date? If so, then just use a little code:

If Not (Me.YourDateField) = Date Then
Me.YourTimeInField.Enabled = False
Me.YourTimeOutField.Enabled = False
Else
Me.YourTimeInField.Enabled = True
Me.YourTimeOutField.Enabled = True
End If

HTH

 

[rangersedge]

Basicly I enter the work date and then the times in and out below. At the moment I have to enter the date each time I enter a time. I'm just trying to simplify it a little and make it so I enter the work date and then the times and it adds up the hours. There are occasions where the times can run into the next day (as in working through the night). On those occasions I can enter the date in that time field. But most of the time it is all during the same day. I hope this makes sense...

 

[burrina]

I'm confused then? What is you want to do and when/where? Form/Query?
Obviously if time can go into the next day what I posted won't work.

 

[rangersedge]

I just want the time and date fields on my form for times in and out to default the date to whatever the date worked field is. This way I don't have to enter the date every time. For 1 person working I have to enter the date at least 5 times. It's possible to have to enter the date 21 times. I want to just enter the start and end time but be able to change the date on the end time if needed.

 

[burrina]

I don't know what format you have you date field or time fields set to. But you can use the OnCurrentEvent and AfterUpDateEvents to update your TimeIn and TimeOut fields.

Example: Using format Long Time for TimeIn and TimeOut and General Date for Work Date.

Me.tmein = Me.wkdte
Me.Dirty = False

HTH

 

[vbaInet]

I'm a bit confused too. Does the time IN/OUT both have a Date and Time parts to them? Can you show us some sample (fictitious) records in a spreadsheet (including the field names). The pdf you attached in your OP doesn't explain anything.

 

[MarkK]

In Access and in VBA the 'date' data type is a floating point number that stores the date AND time in the same field/variable. Given the application you describe--where your times may span from one day to the next--I would store complete date/time information for every data point. Anything less and you can't reliably do math with your data, like to calculate elapsed times, which would be a waste.

What I might do in that case is provide a data entry form that is not bound to the table data, then error check the data entry, prompt the user to confirm that times after midnight do indeed occur on the following day, and then add the date data to the time data before saving it to the table. That would give you maximum control without expecting your user to enter dates AND times for every data point.

Keep in mind that date formats that you might apply have no impact on what is stored in the table. Date formats alter output, not input.

 

[rangersedge]

All the fields are simple Date/Time fields. I have them formated on the form to display only date or only time, but they all store both date and time information. All I need to do is set the date portion of the TimeIn text box to the date in the WorkDate field.

If I need to change the format or anything then that's fine. Majority of the time the times are during the same date. Only occasionally do they run into the next day. In those cases I can set up another form or something?

If I'm going about this wrong and need to start over and approach in a differant way then let me know and let me know how to set this up. Ideally the user would enter the date in the "Date Worked" field and then only the times in the Time IN/OUT fields and the systems calculates the hours worked.

I'm attaching a sample of my setup. Let me know any changes or corrections I should make. It is a cut down of a larger database so some functions or buttons may not work. Just click on the "Timesheet" button and then the "Past Time" button to get to some info. Or you can enter some sample data in the current week form.

Well... I'm trying to attach a copy....

 

[vbaInet]

Well... I'm trying to attach a copy....

Try zipping it.

 

[rangersedge]

See if this works. I had to delete the reports. Should't effect what I need help with any way.

 

[vbaInet]

I'm not sure where I should be looking or what I should be looking for. Please give directions.

 

[MarkK]

I would have a table for each "Punch." I would record the direction, like In our Out, as a -1 or a +1.

tPunch
PunchID (Primary Key)
EmployeeID (Foreign Key)
Direction (-1 for in, +1 for out)
PunchDateTime

Then, since a date/time value is just a number, calculating Hours is very easy . . .

SELECT Sum(Direction * PunchDateTime) As Hours
FROM tPunch
WHERE EmployeeID = 1234
AND PunchDateTime Between #1/1/2014# AND #12/31/2014#

. . . and error checking that is really easy because if you've missed a punch somewhere the math will yield Years not hours. Also check if the math is positive or negative, and if negative you've missed a punch out.

Calculating hours from the data as you've structured it--multiple "punches" per row--and error checking, will be much harder, IMO.

 

[rangersedge]

I have that first form because of the way I filtered to the current week, It has a hidden date range and the timesheet form is filtered from that. Just click on the timesheet button and there you are...

If you enter a date in the date worked field in the top right corner of the form, then I want the Times In and Out to default to whatever date that is. I can't do just a simple "Now()" or "Today()" type thing since some guys wait up to a week before entering their times. Otherwise the date will default to "12/30/1899", and when the guy enters the date and time for a job that runs past midnight... well... you see my problem...

 

[rangersedge]

I found this thread but I could use some clarification.

http://www.access-programmers.co.uk/forums/showthread.php?t=241139&highlight=date

Would I use this code but swap it so that it reads the Date Worked field?
"lost focus code of me.datefield = Date & " " & Left(me.text1,2) & ":" &Right( me.text1,2)"

Maybe something like this?
"lost focus code of me.TimeIn = Date & " " & Left(me.DateWorked,2) & ":" &Right( me.DateWorked,2)"

 

[vbaInet]

Almost forgot about you. I'll download your db and have a look.

 

[vbaInet]

Right, do all those TimeIns/Outs correspond to the DateWorked field? That is, all of them will only correspond to DateWorked for that record.

 

[rangersedge]

Majority of the time. There are days when they will run into you next day

 

[vbaInet]

So is that field supposed to show date and time to the user? For example,

TimeIn: 23/07/2014 20:08

 

[rangersedge]

I want it to be just a time field. The main thing holding me up is the occasion that times run into the next day. I'm testing out a method using an unbound text box and some vba code