By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,948 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,948 IT Pros & Developers. It's quick & easy.

Why won't this work in my report?!

100+
P: 147
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
Feb 11 '08 #1
Share this Question
Share on Google+
15 Replies


puppydogbuddy
Expert 100+
P: 1,923
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])
Feb 11 '08 #2

100+
P: 147
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
Feb 11 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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], "")
Feb 11 '08 #4

100+
P: 147
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.

??
Feb 11 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Feb 11 '08 #6

100+
P: 147
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
Feb 11 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
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], ""))))))
Feb 11 '08 #8

100+
P: 147
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.
Feb 11 '08 #9

100+
P: 147
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?
Feb 11 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
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", ""))))))
Feb 11 '08 #11

cori25
P: 83
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.
Feb 11 '08 #12

100+
P: 147
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
Feb 11 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
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], ""))))))
Feb 12 '08 #14

cori25
P: 83
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
Feb 12 '08 #15

100+
P: 147
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
Feb 14 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.