Day Shift and Night Shift (1 Viewer)

[kjbrack]

Need help with setting a criteria in a query.

Table is called Employee. I use this query to select your name from a combo box on a form. I am wanting it to only show the names of employee's on day shift during those hours and night shift during their hours. I have A field named "Shift" it has Day or Night.

What I'm trying to do:

Night = Between (current time.value) "00:00" and "03:00"
Day = Between (current time.value) "7:00" and "16:30"
Night = Between (current time.value) "16:31" and "23:59"


:banghead::banghead::banghead::banghead::banghead:

 

[arnelgp]

Does the table have date/time field.

 

[kjbrack]

No Date/Time

I use the table to set an employee name to number. The query is right now only used to show active employees. So right now we have 259 different names, but employee's still working for us that are active is 88.

 

[arnelgp]

I think you need to add one. Its difficult to distinguish people on night shift because of 2 scheds.

 

[kjbrack]

Here is how the query looks ran.

Day and night are distinguish in the shift field.




Is there a way to tell access to look at the present time and only pick the day or night.

 

[kjbrack]

Our Day shift is from 7:00 am till 16:30

Our Night Shift is from 16:31 till 3:00 the next day

 

[arnelgp]

Add criteria to Shift:

Iif(Time() between #7:09 AM# AND #4:30 PM#, "Day", "Night")

 

[kjbrack]

Made a few adjustments and it works. Here is a snap shot.




Thanks

 

[Gasman]

Curious now, what happens if you have Night as 16:31 to 03:00 ?

 

[Mark_]

You could also do it with one go...

IIF(Time() Between #7:00:00 AM# and #4:30:00 PM#,"Day","Night)

 

[kjbrack]

Here is how it is working:

Between the time of 07:00:00 AM and 04:30:00 PM
Only Employee's with Day listed in their shift field are shown.

Between the time of 04:30:01 PM and 03:00:00 AM
Only Employee's with Night listed in their shift field are shown.

Between the time of 03:00:01 AM and 06:59:59 AM
No Employee's are shown. No one should be working.

Just what I wanted. Now a night shift employee can't create a data entry using a day shift name or vise versa. I'm using this query on a combo box on a form for workers to record there daily production in a manufacturing environment.

 

[kjbrack]

Curious now, what happens if you have Night as 16:31 to 03:00 ?

Only shows employee's with night in their shift field