Automatic update query to insert specific time only

[RichardM via AccessMonster.com]

I have a table with fields ClockIn (Long Date format)and ClockOut(Long Date
format) fields. It enables me to follow how much a certain person has spent
time at work. The problem is that some people are forgoting to clock out.
To avoid it I would like to run a specific time a update query automaticly
and update the ClockOut fields (where missing) with specific time (15:45). To
do the query it is not a problem but how could I only update the time part
and not the date? Also how could I run the query on specific time?'

Richard

 

[John Spencer]

If your ClockOut field contains a date and time, you should be able to use

ClockOut = DateAdd("n",915,DateValue(ClockIn))

That adds 915 minutes (15 hours and 15 minutes) to midnight of the
ClockIn date.

As for running such a query at a specific time I have no solution to
propose.


UPDATE YourTable
SET ClockOut = DateAdd("n",915,DateValue(ClockIn))
WHERE ClockIn is not null and Clockout is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

I have a table with fields ClockIn (Long Date format)and ClockOut(Long Date
format) fields. It enables me to follow how much a certain person has spent
time at work. The problem is that some people are forgoting to clock out.
To avoid it I would like to run a specific time a update query automaticly
and update the ClockOut fields (where missing) with specific time (15:45). To
do the query it is not a problem but how could I only update the time part
and not the date? Also how could I run the query on specific time?'

Richard

So you want to have a query run automatically at 18:00 or some such time, and
update ClockOut to that day at 15:45 for all records where ClockOut is null?

If so, a query such as

UPDATE tablename
SET ClockOut = Date() + #15:45#
WHERE ClockOut IS NULL
AND ClockIn > Date();

This will find records where the person has clocked in today but not clocked
out at all, and for those records add 15 hours and 45 minutes to midnight at
the start of the current day.

You could also update it to

DateAdd("n", 945, Date())

to get the same effect.