Iif help required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - a bit of context ....

* I have an ACCESS report which sources it's data from an EXCEL spreadsheet.
This s/sheet is a departmental schedule which shows multiple agent start,
break and finish times etc
* The ACCESS report is the INDIVIDUAL schedule for the week, for each agent.
* The linked table automatically formats the time fields as h:nn AM/PM

In my report I want my start field to recognise following conditions like:

IF the agent's start time was at 1:00 AM - then they were on a 'rostered day
off"
IF the agent's start time was at 2:00 AM - then they were on 'Annual Leave'
IF the agent's start time was at 3:00 AM - then they were on 'Sign Ups' etc,
etc

I have tried a number of Iif statements that come back as error as they
don't seem to be recognising the time values, where am I going wrong?

Iif([Start]="1:00 AM","RDO",[Start])
Iif([Start]="1:00:00 a.m.","RDO",[Start])
Iif([Start]=1,"RDO,[Start]))

TIA
 
Hi, Sue.

Date/Time data types must be compared to values enclosed in the # sign. For
example, to compare a time with the short time format:

IIF(Start = #1:00 AM#, "RDO", Start)

.. . . or if you need this all in a single statement to check for any of the
three conditions:

IIF(Start = #1:00 AM#, "RDO", IIF(Start = #2:00 AM#, "Annual Leave",
IIF(Start = #3:00 AM#, "Sign Ups", "Other")))

.. . . where "Other" is given when the value of Start is not 1 AM, 2 AM, or 3
AM.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi Gunny

Thanks, however I tried the following formula and it's still come back error

=IIf([start]=#1:00:00 a.m.#,"RDO",IIf([start]=#2:00:00
a.m.#,"SPARE",IIf([start]=#3:00:00 a.m.#,"SICK",IIf([start]=#4:00:00
a.m.#,"ANNL",IIf([start]=#5:00:00 a.m.#,"Sign Ups",IIf([start]=#6:00:00
a.m.#,"Call Quality",IIf([start]=#7:00:00 a.m.#,"VACANT",[Start])))))))

NB - when I enter in #1:00 AM# - it defualts to the formatting above ...




--
Sue Compelling


'69 Camaro said:
Hi, Sue.

Date/Time data types must be compared to values enclosed in the # sign. For
example, to compare a time with the short time format:

IIF(Start = #1:00 AM#, "RDO", Start)

. . . or if you need this all in a single statement to check for any of the
three conditions:

IIF(Start = #1:00 AM#, "RDO", IIF(Start = #2:00 AM#, "Annual Leave",
IIF(Start = #3:00 AM#, "Sign Ups", "Other")))

. . . where "Other" is given when the value of Start is not 1 AM, 2 AM, or 3
AM.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Sue Compelling said:
Hi - a bit of context ....

* I have an ACCESS report which sources it's data from an EXCEL spreadsheet.
This s/sheet is a departmental schedule which shows multiple agent start,
break and finish times etc
* The ACCESS report is the INDIVIDUAL schedule for the week, for each agent.
* The linked table automatically formats the time fields as h:nn AM/PM

In my report I want my start field to recognise following conditions like:

IF the agent's start time was at 1:00 AM - then they were on a 'rostered day
off"
IF the agent's start time was at 2:00 AM - then they were on 'Annual Leave'
IF the agent's start time was at 3:00 AM - then they were on 'Sign Ups' etc,
etc

I have tried a number of Iif statements that come back as error as they
don't seem to be recognising the time values, where am I going wrong?

Iif([Start]="1:00 AM","RDO",[Start])
Iif([Start]="1:00:00 a.m.","RDO",[Start])
Iif([Start]=1,"RDO,[Start]))

TIA
Click to expand...
Click to expand...
 
try this, I'm not sure what the type of the field so

=IIf(val([start])=1,"RDO",IIf(val([start])=2,"SPARE",IIf(val([start])=3,"SICK",IIf(val([start])=4,"ANNL",IIf(val([start])=5,"Sign
Ups",IIf(val([start])=6,"Call
Quality",IIf(val([start])=7,"VACANT",[Start])))))))
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Sue Compelling said:
Hi Gunny

Thanks, however I tried the following formula and it's still come back error

=IIf([start]=#1:00:00 a.m.#,"RDO",IIf([start]=#2:00:00
a.m.#,"SPARE",IIf([start]=#3:00:00 a.m.#,"SICK",IIf([start]=#4:00:00
a.m.#,"ANNL",IIf([start]=#5:00:00 a.m.#,"Sign Ups",IIf([start]=#6:00:00
a.m.#,"Call Quality",IIf([start]=#7:00:00 a.m.#,"VACANT",[Start])))))))

NB - when I enter in #1:00 AM# - it defualts to the formatting above ...




--
Sue Compelling


'69 Camaro said:
Hi, Sue.

Date/Time data types must be compared to values enclosed in the # sign. For
example, to compare a time with the short time format:

IIF(Start = #1:00 AM#, "RDO", Start)

. . . or if you need this all in a single statement to check for any of the
three conditions:

IIF(Start = #1:00 AM#, "RDO", IIF(Start = #2:00 AM#, "Annual Leave",
IIF(Start = #3:00 AM#, "Sign Ups", "Other")))

. . . where "Other" is given when the value of Start is not 1 AM, 2 AM, or 3
AM.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Sue Compelling said:
Hi - a bit of context ....

* I have an ACCESS report which sources it's data from an EXCEL spreadsheet.
This s/sheet is a departmental schedule which shows multiple agent start,
break and finish times etc
* The ACCESS report is the INDIVIDUAL schedule for the week, for each agent.
* The linked table automatically formats the time fields as h:nn AM/PM

In my report I want my start field to recognise following conditions like:

IF the agent's start time was at 1:00 AM - then they were on a 'rostered day
off"
IF the agent's start time was at 2:00 AM - then they were on 'Annual Leave'
IF the agent's start time was at 3:00 AM - then they were on 'Sign Ups' etc,
etc

I have tried a number of Iif statements that come back as error as they
don't seem to be recognising the time values, where am I going wrong?

Iif([Start]="1:00 AM","RDO",[Start])
Iif([Start]="1:00:00 a.m.","RDO",[Start])
Iif([Start]=1,"RDO,[Start]))

TIA
Click to expand...
Click to expand...
Click to expand...
 
Thanks to you both - I'm a very happy woman. Cheers
--
Sue Compelling


Ofer said:
try this, I'm not sure what the type of the field so

=IIf(val([start])=1,"RDO",IIf(val([start])=2,"SPARE",IIf(val([start])=3,"SICK",IIf(val([start])=4,"ANNL",IIf(val([start])=5,"Sign
Ups",IIf(val([start])=6,"Call
Quality",IIf(val([start])=7,"VACANT",[Start])))))))
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Sue Compelling said:
Hi Gunny

Thanks, however I tried the following formula and it's still come back error

=IIf([start]=#1:00:00 a.m.#,"RDO",IIf([start]=#2:00:00
a.m.#,"SPARE",IIf([start]=#3:00:00 a.m.#,"SICK",IIf([start]=#4:00:00
a.m.#,"ANNL",IIf([start]=#5:00:00 a.m.#,"Sign Ups",IIf([start]=#6:00:00
a.m.#,"Call Quality",IIf([start]=#7:00:00 a.m.#,"VACANT",[Start])))))))

NB - when I enter in #1:00 AM# - it defualts to the formatting above ...




--
Sue Compelling


'69 Camaro said:
Hi, Sue.

Date/Time data types must be compared to values enclosed in the # sign. For
example, to compare a time with the short time format:

IIF(Start = #1:00 AM#, "RDO", Start)

. . . or if you need this all in a single statement to check for any of the
three conditions:

IIF(Start = #1:00 AM#, "RDO", IIF(Start = #2:00 AM#, "Annual Leave",
IIF(Start = #3:00 AM#, "Sign Ups", "Other")))

. . . where "Other" is given when the value of Start is not 1 AM, 2 AM, or 3
AM.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi - a bit of context ....

* I have an ACCESS report which sources it's data from an EXCEL spreadsheet.
This s/sheet is a departmental schedule which shows multiple agent start,
break and finish times etc
* The ACCESS report is the INDIVIDUAL schedule for the week, for each agent.
* The linked table automatically formats the time fields as h:nn AM/PM

In my report I want my start field to recognise following conditions like:

IF the agent's start time was at 1:00 AM - then they were on a 'rostered day
off"
IF the agent's start time was at 2:00 AM - then they were on 'Annual Leave'
IF the agent's start time was at 3:00 AM - then they were on 'Sign Ups' etc,
etc

I have tried a number of Iif statements that come back as error as they
don't seem to be recognising the time values, where am I going wrong?

Iif([Start]="1:00 AM","RDO",[Start])
Iif([Start]="1:00:00 a.m.","RDO",[Start])
Iif([Start]=1,"RDO,[Start]))

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top