Why won't this work in my report?!

[DAHMB]

I have a report called rptCarSheet that is grouped on a field called ShiftID. In the ShiftID Group I have several fields that I am trying to input data by the following expression:

[exp] =IIf([AreaID]="1",[Employee]) [exp]

for each field I have changed the AreaID value the the value I need "2" "3" etc.

My problem is that it only works for the first instance entered. I have tried it in the details section as well and get the same issue.

Please tell me what I am doing wrong
Thanks
Dan

[puppydogbuddy]

I have a report called rptCarSheet that is grouped on a field called ShiftID. In the ShiftID Group I have several fields that I am trying to input data by the following expression:

[exp] =IIf([AreaID]="1",[Employee]) [exp]

for each field I have changed the AreaID value the the value I need "2" "3" etc.

My problem is that it only works for the first instance entered. I have tried it in the details section as well and get the same issue.

Please tell me what I am doing wrong
Thanks
Dan

First of all, you do not have the correct syntax for the IIf statement. The general syntax is like this:
= IIf(expression, value if true, value if false)

secondly, if AreaID is a numeric data type, you don't put quotes around the return value, quotes are for text data types.

so try your IIf this way, assuming you want the value in the Employee field returned when the AreaID = 1, and you want the value in the exp field returned if the AreaID is not equal to one:

=IIf([AreaID]=1,[Employee], [exp])

[DAHMB]

First of all, you do not have the correct syntax for the IIf statement. The general syntax is like this:
= IIf(expression, value if true, value if false)

secondly, if AreaID is a numeric data type, you don't put quotes around the return value, quotes are for text data types.

so try your IIf this way, assuming you want the value in the Employee field returned when the AreaID = 1, and you want the value in the exp field returned if the AreaID is not equal to one:

=IIf([AreaID]=1,[Employee], [exp])

Thanks for the reply,
Actually there is no exp field, that was an accident of my attempt to enclosed my expression to make it stand out from the rest of my post. My expression is
=IIf([AreaID]=1,[WorkingOfficer]) I have removed the quottes from the number as you said, because AreaID is a number field. HHowever it still only works for the first field in the group. Any ideas?
Dan

[puppydogbuddy]

Thanks for the reply,
Actually there is no exp field, that was an accident of my attempt to enclosed my expression to make it stand out from the rest of my post. My expression is
=IIf([AreaID]=1,[WorkingOfficer]) I have removed the quottes from the number as you said, because AreaID is a number field. HHowever it still only works for the first field in the group. Any ideas?
Dan

you do not have the correct syntax for the IIf statement. The general syntax is like this:
= IIf(expression, value if true, <<<value if false>>>)

=IIf([AreaID]=1,[WorkingOfficer], "")

[DAHMB]

you do not have the correct syntax for the IIf statement. The general syntax is like this:
= IIf(expression, value if true, <<<value if false>>>)

=IIf([AreaID]=1,[WorkingOfficer], "")

Thanks again but still no luck, It still fills the first field only. I also tried
=IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer])
and then it returned the first value from the WorkingOfficer field every time regardless of the AreaID I entered.

??

[puppydogbuddy]

Thanks again but still no luck, It still fills the first field only. I also tried
=IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer])
and then it returned the first value from the WorkingOfficer field every time regardless of the AreaID I entered.

??

What happened when you posted the code I gave you (see below) ?
=IIf([AreaID]=1,[WorkingOfficer], "")

Please post all of your code related to an IIf statement.

[DAHMB]

What happened when you posted the code I gave you (see below) ?
=IIf([AreaID]=1,[WorkingOfficer], "")

Please post all of your code related to an IIf statement.

When I tried th ecode you gave me it filled in the first field and left all the others blank
I have 10 fields in the group, six of them are based on the code we are trying to fix they are:
=IIf([AreaID]=1,[WorkingOfficer], "")
=IIf([AreaID]=2,[WorkingOfficer], "")
=IIf([AreaID]=3,[WorkingOfficer], "")
=IIf([AreaID]=4,[WorkingOfficer], "")
=IIf([AreaID]=5,[WorkingOfficer], "")
=IIf([AreaID]=6,[WorkingOfficer], "")
The above fields are named simply Off1 through Off6

Thanks for all your help.
Dan

[puppydogbuddy]

When I tried th ecode you gave me it filled in the first field and left all the others blank
I have 10 fields in the group, six of them are based on the code we are trying to fix they are:
=IIf([AreaID]=1,[WorkingOfficer], "")
=IIf([AreaID]=2,[WorkingOfficer], "")
=IIf([AreaID]=3,[WorkingOfficer], "")
=IIf([AreaID]=4,[WorkingOfficer], "")
=IIf([AreaID]=5,[WorkingOfficer], "")
=IIf([AreaID]=6,[WorkingOfficer], "")
The above fields are named simply Off1 through Off6

Thanks for all your help.
Dan

If the above fields are named simply Off1 through Off6
=IIf([AreaID]=1,[Off1],IIf([AreaID]=2,[Off2],IIf([AreaID]=3,[Off3],IIf([AreaID]=4,[Off4], IIf([AreaID]=5,[Off5],IIf([AreaID]=6,[Off6], ""))))))

[DAHMB]

If the above fields are named simply Off1 through Off6
=IIf([AreaID]=1,[Off1],IIf([AreaID]=2,[Off2],IIf([AreaID]=3,[Off3],IIf([AreaID]=4,[Off4], IIf([AreaID]=5,[Off5],IIf([AreaID]=6,[Off6], ""))))))

No I ment the fields I am trying to fill are named Off1 through Off6 for example the in the controlsource box of Off1 I have =IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer]) and so on.

[DAHMB]

No I ment the fields I am trying to fill are named Off1 through Off6 for example the in the controlsource box of Off1 I have =IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer]) and so on.

I also could be going about this all wrong I ama trying to enter the value of the WorkingOfficer Where the AreaID field is the value I enter and the only way I could think of was IIF, is there a WHERE statement I should use instead?

[puppydogbuddy]

No I ment the fields I am trying to fill are named Off1 through Off6 for example the in the controlsource box of Off1 I have =IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer]) and so on.

The syntax you are using makes no sense. If Off1 thru Off6 are the values you want to fill for the field [WorkingOfficer], then place the following in the field [WorkingOfficer]:

=IIf([AreaID]=1,"Off1",IIf([AreaID]=2,"Off2",IIf([AreaID]=3,"Off3",IIf([AreaID]=4,"Off4", IIf([AreaID]=5,"Off5",IIf([AreaID]=6,"Off6", ""))))))

[cori25]

Try This:

=iif([AreaID]=1, "WorkingOfficer", "")+iif([AreaID]=2, "WorkingOfficer", "")+iif([AreaID]=3, "WorkingOfficer", "")...

I have put "WorkingOfficer" in parenthesis because I am unaware it this is an existing field or what you would like to have shown as the field. If it is an existing field, put in brackets.

*Reminder* When closing this statement remember you will need to have as many closing parenthesis as there are statements.

Hope this helps

No I ment the fields I am trying to fill are named Off1 through Off6 for example the in the controlsource box of Off1 I have =IIf([AreaID]=1,[WorkingOfficer],[WorkingOfficer]) and so on.

[DAHMB]

Try This:

=iif([AreaID]=1, "WorkingOfficer", "")+iif([AreaID]=2, "WorkingOfficer", "")+iif([AreaID]=3, "WorkingOfficer", "")...

I have put "WorkingOfficer" in parenthesis because I am unaware it this is an existing field or what you would like to have shown as the field. If it is an existing field, put in brackets.

*Reminder* When closing this statement remember you will need to have as many closing parenthesis as there are statements.

Hope this helps

I'm sorry I have confused you
The empty field in the report are called Off1, Off2, Off3 Off4,Off5, Off6
I want to fill those fields with the value of the WorkingOfficer field from my query where the AreaID field in the query is equal to the value entered in the expression.

Thanks sorry for my confusions I am very green at this

[puppydogbuddy]

I'm sorry I have confused you
The empty field in the report are called Off1, Off2, Off3 Off4,Off5, Off6
I want to fill those fields with the value of the WorkingOfficer field from my query where the AreaID field in the query is equal to the value entered in the expression.

Thanks sorry for my confusions I am very green at this

What happened when you ran my code from Post # 8 (see below)?

If the above fields are named simply Off1 through Off6
=IIf([AreaID]=1,[Off1],IIf([AreaID]=2,[Off2],IIf([AreaID]=3,[Off3],IIf([AreaID]=4,[Off4], IIf([AreaID]=5,[Off5],IIf([AreaID]=6,[Off6], ""))))))

[cori25]

Try This:

=iif([AreaID]=1, "WorkingOfficer", "")+iif([AreaID]=2, "WorkingOfficer", "")+iif([AreaID]=3, "WorkingOfficer", "")...

I have put "WorkingOfficer" in parenthesis because I am unaware it this is an existing field or what you would like to have shown as the field. If it is an existing field, put in brackets.

*Reminder* When closing this statement remember you will need to have as many closing parenthesis as there are statements.

Hope this helps

[DAHMB]

Try This:

=iif([AreaID]=1, "WorkingOfficer", "")+iif([AreaID]=2, "WorkingOfficer", "")+iif([AreaID]=3, "WorkingOfficer", "")...

I have put "WorkingOfficer" in parenthesis because I am unaware it this is an existing field or what you would like to have shown as the field. If it is an existing field, put in brackets.

*Reminder* When closing this statement remember you will need to have as many closing parenthesis as there are statements.

Hope this helps

No luck, thanks everyone but I gave up and have found a work around.

Thanks