Automatic shift change based on time

[Guest]

I have a form that currently prompts workers to enter their shift number (1
or 2). I would like to automate this so if the time is between 3:30 AM and
3:30 PM, the shift is 1, with a shift of 2 at all other times. I have tried
using a set value macro with an if statement of

iif (Time() between #3:30:00am# and #3:30:00pm#, 1, 2)

but I am not able to use the time() function here (I do not use it anywhere
else in the database).

How can I automatically set the shifts?

Thank you

 

[Douglas J Steele]

What do you mean by "not able to use the time() function here"? Are you
saying that trying to use it raises an error?

It could be a case that your References collection has got messed up.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module. Select Tools
| References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

I

 

[Guest]

To clarify, when I try to run the macro, I get the error message "Unknown
function 'Time' in validation expression or default value on 'defects.time'".


I did find that Time() is a value in the table "defects".

Thank you

 

[Guest]

I also found this code for shift change in an earlier version of the form I
am working on, but it does not calculate the shifts correctly:

Private Sub Command15_Click()
DoCmd.Maximize

Dim current_time As Date
current_time = Time

If (current_time < TimeValue("3:30:00 PM")) Then
Me.Shift.Value = 1
ElseIf (current_time > TimeValue("3:30:00 PM")) Then
Me.Shift.Value = 2
Else
Me.Shift.Value = 0
End If

On Error GoTo Err_Command15_Click
Dim s As Integer
s = Me![Shift]

DoCmd.GoToRecord , , acNewRec
Me.Shift.SetFocus
Me.Shift.Text = s
Me.Serial_Number.SetFocus

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

 

[Douglas J Steele]

What do you mean by "Time() is a value in the table..."?

Do you have a field named Time? Rename it. Time is a reserved word, and
shouldn't be used for any of your own objects (fields, variables, etc.)

While you're working around fixing the problem, see whether the following
works:

iif (VBA.Time() between #3:30:00am# and #3:30:00pm#, 1, 2)

However, fix the problem even if that works: you'll likely get bitten again
if you don't.

 

[Guest]

Thank you for you input.

Time is a column name; I will rename it as soon as I can have sole access to
the table later today.


I tried your expression:

iif (VBA.Time() between #3:30:00am# and #3:30:00pm#, 1, 2)

and got the error message "Microsoft Access can't parse the expression".

 

[Douglas J Steele]

Try

iif (VBA.Time() >= #3:30:00am# and VBA.Time() <= #3:30:00pm#, 1, 2)

 

[Guest]

I get the same message.

Try

iif (VBA.Time() >= #3:30:00am# and VBA.Time() <= #3:30:00pm#, 1, 2)

 

[Douglas J Steele]

Okay, where is the expression? All you've shown is the IIf statement, not
how it's being used.

 

[Guest]

I am using the expression in the "Expression" box of a set value macro.

Thanks

 

[Douglas J. Steele]

Sorry, I never use macros, so don't have any more suggestions.

 

[Guest]

Thank you for your help.

Sorry, I never use macros, so don't have any more suggestions.