replace one value with another when concatenting fields in a repor

[Guest]

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]

 

[Guest]

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

I noticed that some people write it that way, try it, to see if that the case

=IIf([QualifierDescription]="none";"";[QualifierDescription] & " ") &
[RoleDescription]

--
I hope that helped
Good luck

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

:

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

Still no good. I still get the same syntax error. This is very odd. What
could be causing this problem? I have tried using the IIF statement as
criteria, field header, and expression all with the same result. This is
frustrating.

I noticed that some people write it that way, try it, to see if that the case

=IIf([QualifierDescription]="none";"";[QualifierDescription] & " ") &
[RoleDescription]

--
I hope that helped
Good luck

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck


:

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

:

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

Let start from the begining, something I should have asked you before, where
do you use this iif?
1. If its in the record source of the report the use this

Select Field1, Field2, IIf([Field1]="None","",[Field1] & " ") & [Field2] as
Field3 From TableName

Then you can use field3 to be displayed in the report

2. To use it as the ControlSource Property of a field in report then use

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
' Be sure that this fields in the report

--
I hope that helped
Good luck

Still no good. I still get the same syntax error. This is very odd. What
could be causing this problem? I have tried using the IIF statement as
criteria, field header, and expression all with the same result. This is
frustrating.

I noticed that some people write it that way, try it, to see if that the case

=IIf([QualifierDescription]="none";"";[QualifierDescription] & " ") &
[RoleDescription]

--
I hope that helped
Good luck

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

:

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck


:

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

:

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

Ofer thanks for sticking with me. I finally got it to work, but it sure is
strange what is happening. When I pasted or typed the statement in Query
Design View the commas were converted to pipes | and caused an error. I got
it to work by staying in SQL View. If made the mistake of switching to Query
Design View again, I would have to cut the IIF statement, go back to SQL
View, paste, change the pipes to commas, and then save. I still think there
must be a preference setting somewhere that is causing this behavior. Thanks
again.

Let start from the begining, something I should have asked you before, where
do you use this iif?
1. If its in the record source of the report the use this

Select Field1, Field2, IIf([Field1]="None","",[Field1] & " ") & [Field2] as
Field3 From TableName

Then you can use field3 to be displayed in the report

2. To use it as the ControlSource Property of a field in report then use

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
' Be sure that this fields in the report

--
I hope that helped
Good luck

Still no good. I still get the same syntax error. This is very odd. What
could be causing this problem? I have tried using the IIF statement as
criteria, field header, and expression all with the same result. This is
frustrating.

I noticed that some people write it that way, try it, to see if that the case

=IIf([QualifierDescription]="none";"";[QualifierDescription] & " ") &
[RoleDescription]

--
I hope that helped
Good luck


:

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

:

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck


:

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

:

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor

 

[Guest]

I don't know wy that happen, mybe start a new post asking that question, mybe
somebody else know the reason for that.
Sorry

--
I hope that helped
Good luck

Ofer thanks for sticking with me. I finally got it to work, but it sure is
strange what is happening. When I pasted or typed the statement in Query
Design View the commas were converted to pipes | and caused an error. I got
it to work by staying in SQL View. If made the mistake of switching to Query
Design View again, I would have to cut the IIF statement, go back to SQL
View, paste, change the pipes to commas, and then save. I still think there
must be a preference setting somewhere that is causing this behavior. Thanks
again.

Let start from the begining, something I should have asked you before, where
do you use this iif?
1. If its in the record source of the report the use this

Select Field1, Field2, IIf([Field1]="None","",[Field1] & " ") & [Field2] as
Field3 From TableName

Then you can use field3 to be displayed in the report

2. To use it as the ControlSource Property of a field in report then use

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
' Be sure that this fields in the report

--
I hope that helped
Good luck

Still no good. I still get the same syntax error. This is very odd. What
could be causing this problem? I have tried using the IIF statement as
criteria, field header, and expression all with the same result. This is
frustrating.

:

I noticed that some people write it that way, try it, to see if that the case

=IIf([QualifierDescription]="none";"";[QualifierDescription] & " ") &
[RoleDescription]

--
I hope that helped
Good luck


:

I simply pasted your code into the Query header and changed the field names
to the actual field names. Here is what I tried -
=IIf([QualifierDescription]="none","",[QualifierDescription] & " ") &
[RoleDescription]

:

I tried it, and I got no error
Can you post your code?

--
I hope that helped
Good luck


:

Ofer, thanks for the quick response. I tried the IIF statement you provided,
but I'm getting a syntax error at or after the comma, after the word "none".
any ideas?

:

Try this

=IIf([Field1]="None","",[Field1] & " ") & [Field2]
--
I hope that helped
Good luck


:

Here is a problem I am having related to how information is displayed in a
report. I have two fields I'm trying to concatenate. If the the word "none"
appears in the first field I want to disregard that value and have the
combined fields display just the value of the second field. I can't make or
leave the first field's value null because it is a key field. I can't
permanently combine the two fields because of other reporting needs. So far I
have been unsuccessful in using the IIF statement to get proper results.

Table example:

Field1 Field 2 Desirded Result
record1 none Faculty Faculty
record2 Academic Representative Academic Rep
record3 Academic Counselor Academic Counselor