query for times over differrent days

[gaz100uk]

Hi there, Sorry if this rambles or doesn’t make sense but please bear with me.
I have a little experience with access many years ago and now trying to get back into it. The problem i have is as follows.

I’m trying to quiz a large excel spread sheet. It is basically made up of all our jobs manufactured and peoples clocking in and out on these jobs to manufacture them. I have managed to create a query that tells me all the items worked on for a given day. The trouble comes with the jobs worked on by the night shift. If i was to look at everything for the 1st of July it won’t return the items they started in the early hours of the 2nd but includes the items they started in the early hours of the 1st, which was on the previous days shift. (Hope that makes sense).

The field’s i have to query on are:
{REAL_CLOCKIN] & [REAL_CLOCKOUT]. The trouble is these are not actual date/time field’s but stored as a text field. There formatted as 2011-04-09 08:10:39.000



I have managed to create a query that splits these columns into 2. 1 with a date and 1 with a time.
Date In: Left([REAL_CLOCKIN],InStr(1,[REAL_CLOCKIN]," ")-1)
and
Time: Right([REAL_CLOCKIN],Len([REAL_CLOCKIN])-InStr(1,[REAL_CLOCKIN]," "))

Can anyone point me in a direction, apart from give up, that would return the info i need.

Many thanks in advance

[June7]

Not sure what you are asking for. You want to return records for items they started in the early hours of the 2nd? If the original format is the cause of problem, perhaps use of the CDate function will solve it.

[gaz100uk]

I thought i had rambled on to much...

What we are trying to do is list all the jobs staff worked on in their shift. Day shift is easy its nights are the issue. So for example some jobs would have been started at 6pm on the 5th and some could have been started at 2am on the 6th, before they went home.

[rpeare]

What is the table construction for the data that tells you which employee worked on which job? or is everyone on the shift on the same job? So for instance Job 1 starts at 8am, ends at 3pm, job 2 starts at 3pm and ends at 1am, job 3 starts at 1am and ends at 9am and everyone that is working a shift is working on the same job?