Calculation/Formula

G

Guest

Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Assuming [TIME_ON] is a date/time field that contains only the time, not a
date,

=IIF([TIME_ON] >= #08:00:00# and time <= #16:00:00#, 1,0)

If it contains a date, you need to stip it out:
=IIF(TimeValue([TIME_ON]) >= #08:00:00# and time <= #16:00:00#, 1,0)
 
G

Guest

Here is the message I get:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Klatuu said:
Assuming [TIME_ON] is a date/time field that contains only the time, not a
date,

=IIF([TIME_ON] >= #08:00:00# and time <= #16:00:00#, 1,0)

If it contains a date, you need to stip it out:
=IIF(TimeValue([TIME_ON]) >= #08:00:00# and time <= #16:00:00#, 1,0)

K said:
Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Thanks,

But same Error. I am placing this Expression in a query. Not sure if that
makes the difference. No the TIME_ON field is only time. No Date

Alex Dybenko said:
Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


K said:
Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Okay got it. Sorry. I did not have the TIME_ON included in the query.Can I
expand the formula to Add 2 in a nother Shift time frame?

Example

=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0) Or
=IIF(TimeValue([TIME_ON]) between #016:00# and #24:00#, 3,0)

Would that work?


Alex Dybenko said:
Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


K said:
Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

You can nest IIf statements. In this case, instead of returning 0 if it is
not between 8 and 4, put in the next test as the false condition.

Since you state that TIME_ON contains only time, the TimeValue function is
just overhead that will slow the query to some degree.

=IIF([TIME_ON] between #08:00# and #16:00#, 1, IIF([TIME_ON] between
#016:00# and #24:00#, 3,0))

K said:
Okay got it. Sorry. I did not have the TIME_ON included in the query.Can I
expand the formula to Add 2 in a nother Shift time frame?

Example

=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0) Or
=IIF(TimeValue([TIME_ON]) between #016:00# and #24:00#, 3,0)

Would that work?


Alex Dybenko said:
Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


K said:
Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Thanks Klatuu:

I tried your Expression,(modified it a little) however now All I am
returning is values of 2

My Expression:

HRC_TO_HRS: IIf([TIME_ON] Between #8:00:00 AM# And #4:00:00
PM#,1,IIf([TIME_ON] Between #4:01:00 PM# And #11:59:00 PM#,3,2))

Klatuu said:
You can nest IIf statements. In this case, instead of returning 0 if it is
not between 8 and 4, put in the next test as the false condition.

Since you state that TIME_ON contains only time, the TimeValue function is
just overhead that will slow the query to some degree.

=IIF([TIME_ON] between #08:00# and #16:00#, 1, IIF([TIME_ON] between
#016:00# and #24:00#, 3,0))

K said:
Okay got it. Sorry. I did not have the TIME_ON included in the query.Can I
expand the formula to Add 2 in a nother Shift time frame?

Example

=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0) Or
=IIF(TimeValue([TIME_ON]) between #016:00# and #24:00#, 3,0)

Would that work?


Alex Dybenko said:
Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Try putting the TimeValue functions back in. Maybe they do need it.
Good catch on the time overlaps :)

K said:
Thanks Klatuu:

I tried your Expression,(modified it a little) however now All I am
returning is values of 2

My Expression:

HRC_TO_HRS: IIf([TIME_ON] Between #8:00:00 AM# And #4:00:00
PM#,1,IIf([TIME_ON] Between #4:01:00 PM# And #11:59:00 PM#,3,2))

Klatuu said:
You can nest IIf statements. In this case, instead of returning 0 if it is
not between 8 and 4, put in the next test as the false condition.

Since you state that TIME_ON contains only time, the TimeValue function is
just overhead that will slow the query to some degree.

=IIF([TIME_ON] between #08:00# and #16:00#, 1, IIF([TIME_ON] between
#016:00# and #24:00#, 3,0))

K said:
Okay got it. Sorry. I did not have the TIME_ON included in the query.Can I
expand the formula to Add 2 in a nother Shift time frame?

Example

=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0) Or
=IIF(TimeValue([TIME_ON]) between #016:00# and #24:00#, 3,0)

Would that work?


:

Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 
G

Guest

Yeap that is what it needed. Thanks



Klatuu said:
Try putting the TimeValue functions back in. Maybe they do need it.
Good catch on the time overlaps :)

K said:
Thanks Klatuu:

I tried your Expression,(modified it a little) however now All I am
returning is values of 2

My Expression:

HRC_TO_HRS: IIf([TIME_ON] Between #8:00:00 AM# And #4:00:00
PM#,1,IIf([TIME_ON] Between #4:01:00 PM# And #11:59:00 PM#,3,2))

Klatuu said:
You can nest IIf statements. In this case, instead of returning 0 if it is
not between 8 and 4, put in the next test as the false condition.

Since you state that TIME_ON contains only time, the TimeValue function is
just overhead that will slow the query to some degree.

=IIF([TIME_ON] between #08:00# and #16:00#, 1, IIF([TIME_ON] between
#016:00# and #24:00#, 3,0))

:

Okay got it. Sorry. I did not have the TIME_ON included in the query.Can I
expand the formula to Add 2 in a nother Shift time frame?

Example

=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0) Or
=IIF(TimeValue([TIME_ON]) between #016:00# and #24:00#, 3,0)

Would that work?


:

Hi,
try:
=IIF(TimeValue([TIME_ON]) between #08:00# and #16:00#, 1,0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Good day.

I am trying to assign a number between certain times.
For example:
From 8 am to 4 pm is shift 2. How would I put this into Syntax. I tried
this
but does not seem to work

=IIF([TIME_ON]<08:00 and >16:00, 1,0)

Any help would be greatly appreciated. I am placing this calculation in a
query.

Cheers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top