Iif help required

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
 
G

Guest

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.
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 

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

Similar Threads

IIf - Sum Problem 1
IIF Criteria levels 7
Help with Iif on Report 4
Help with IIF expression 6
DATABASE REPORT/CROSSTAB QUERY HELP 2
Excel Facetime Measurement 1
Calculate Face Time 4
Type mismatch in a criteria that used to work 13

Top