Access Question

[faureman via AccessMonster.com]

I have a table that is automatically generating a date/time based using '=now
()'.

I want to create another field that will use the default value to assign the
current shift (based on current hour) but allow it to be changed if necessary.


1st Shift 06:00 - 13:59 (hour >=6 and <14)
2nd Shift 14:00 - 21:59 (hour >=14 and <22)
3rd Shift 22:00 - 06:59 (hour >=22 and <6)

Can someone please help me on the correct expression to use in the Shift
field Defualt Value?

Thanks.

 

[John W. Vinson]

I have a table that is automatically generating a date/time based using '=now
()'.

I want to create another field that will use the default value to assign the
current shift (based on current hour) but allow it to be changed if necessary.


1st Shift 06:00 - 13:59 (hour >=6 and <14)
2nd Shift 14:00 - 21:59 (hour >=14 and <22)
3rd Shift 22:00 - 06:59 (hour >=22 and <6)

Can someone please help me on the correct expression to use in the Shift
field Defualt Value?

Thanks.

YOu cannot do this in the Table. Tables have no usable events, and table
default values cannot reference other fields in the table.

you'll need to enter data using a Form, and use VBA code in some appropriate
event - BeforeInsert I'd guess - to set the shift value.

Why would the person's shift be determined by when data is being entered into
the table, as opposed to when they are actually working???

 

[Graham R Seach]

The following should all appear on one line.

IIf(Hour(Now())<7 Or Hour(Now())>21,3,IIf(Hour(Now())>13 And
Hour(Now())<22,2,1))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[faureman via AccessMonster.com]

This appears to work perfectly!

Thank you.

The following should all appear on one line.

IIf(Hour(Now())<7 Or Hour(Now())>21,3,IIf(Hour(Now())>13 And
Hour(Now())<22,2,1))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I have a table that is automatically generating a date/time based using
'=now

[quoted text clipped - 13 lines]

Thanks.

 

[faureman via AccessMonster.com]

I believe I have been provided my solution.

The reason why I want this is because I am creating a "real-time" defect
tracking database. Operators will fill this out on the fly throughout the
shift. My preference is that they only enter data that they MUST enter - in
order to minimize time and effort for input.

So... if an operator is working and the current time is reported... I wanted
the current shift to be defaulted also. However, I did want them to be able
to modify it should they need to report a backdated shift event (say... after
the shift ends).

Anyway, I hope this clears up my intentions and thankfully, I believe I have
a workable solution.

Thank you for your interest and attempt to help. It is appreciated.

I have a table that is automatically generating a date/time based using '=now
()'.

[quoted text clipped - 10 lines]

Thanks.

YOu cannot do this in the Table. Tables have no usable events, and table
default values cannot reference other fields in the table.

you'll need to enter data using a Form, and use VBA code in some appropriate
event - BeforeInsert I'd guess - to set the shift value.

Why would the person's shift be determined by when data is being entered into
the table, as opposed to when they are actually working???