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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"QuantumLeap" <(E-Mail Removed)> wrote in message
news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> 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

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

"Douglas J Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "QuantumLeap" <(E-Mail Removed)> wrote in message
> news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > 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
>
>
>

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



"QuantumLeap" wrote:

> 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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"QuantumLeap" <(E-Mail Removed)> wrote in message
news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> 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
>
> "Douglas J Steele" wrote:
>
> > 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
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > 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
> >
> >
> >

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" wrote:

> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "QuantumLeap" <(E-Mail Removed)> wrote in message
> news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> > 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
> >
> > "Douglas J Steele" wrote:
> >
> > > 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
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > > 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
> > >
> > >
> > >
>
>
>

Try

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"QuantumLeap" <(E-Mail Removed)> wrote in message
news:5317A040-7F22-4E86-9952-(E-Mail Removed)...
> 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" wrote:
>
> > 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.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> > > 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
> > >
> > > "Douglas J Steele" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > > "QuantumLeap" <(E-Mail Removed)> wrote in
message
> > > > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

I get the same message.

"Douglas J Steele" wrote:

> Try
>
> iif (VBA.Time() >= #3:30:00am# and VBA.Time() <= #3:30:00pm#, 1, 2)
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "QuantumLeap" <(E-Mail Removed)> wrote in message
> news:5317A040-7F22-4E86-9952-(E-Mail Removed)...
> > 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" wrote:
> >
> > > 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.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > > news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> > > > 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
> > > >
> > > > "Douglas J Steele" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > Doug Steele, Microsoft Access MVP
> > > > > http://I.Am/DougSteele
> > > > > (no e-mails, please!)
> > > > >
> > > > >
> > > > > "QuantumLeap" <(E-Mail Removed)> wrote in
> message
> > > > > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"QuantumLeap" <(E-Mail Removed)> wrote in message
news:71247997-994B-4377-81CE-(E-Mail Removed)...
> I get the same message.
>
> "Douglas J Steele" wrote:
>
> > Try
> >
> > iif (VBA.Time() >= #3:30:00am# and VBA.Time() <= #3:30:00pm#, 1, 2)
> >
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > news:5317A040-7F22-4E86-9952-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > 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.
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > > "QuantumLeap" <(E-Mail Removed)> wrote in
message
> > > > news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> > > > > 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
> > > > >
> > > > > "Douglas J Steele" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > > --
> > > > > > Doug Steele, Microsoft Access MVP
> > > > > > http://I.Am/DougSteele
> > > > > > (no e-mails, please!)
> > > > > >
> > > > > >
> > > > > > "QuantumLeap" <(E-Mail Removed)> wrote in
> > message
> > > > > > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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

Thanks

"Douglas J Steele" wrote:

> Okay, where is the expression? All you've shown is the IIf statement, not
> how it's being used.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "QuantumLeap" <(E-Mail Removed)> wrote in message
> news:71247997-994B-4377-81CE-(E-Mail Removed)...
> > I get the same message.
> >
> > "Douglas J Steele" wrote:
> >
> > > Try
> > >
> > > iif (VBA.Time() >= #3:30:00am# and VBA.Time() <= #3:30:00pm#, 1, 2)
> > >
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "QuantumLeap" <(E-Mail Removed)> wrote in message
> > > news:5317A040-7F22-4E86-9952-(E-Mail Removed)...
> > > > 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" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Doug Steele, Microsoft Access MVP
> > > > > http://I.Am/DougSteele
> > > > > (no e-mails, please!)
> > > > >
> > > > >
> > > > > "QuantumLeap" <(E-Mail Removed)> wrote in
> message
> > > > > news5DAF4D1-9092-4478-B2B2-(E-Mail Removed)...
> > > > > > 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
> > > > > >
> > > > > > "Douglas J Steele" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > > --
> > > > > > > Doug Steele, Microsoft Access MVP
> > > > > > > http://I.Am/DougSteele
> > > > > > > (no e-mails, please!)
> > > > > > >
> > > > > > >
> > > > > > > "QuantumLeap" <(E-Mail Removed)> wrote in
> > > message
> > > > > > > news:3B01EC51-9653-4514-877F-(E-Mail Removed)...
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>