How to incorporate If...Then...Else function in a report

  • Thread starter Thread starter Kath
  • Start date Start date
K

Kath

I use Access to make labels, one of which is folder
label. I wonder if I can use "IF...THEN...ELSE" to make
one item on the label to appear as something else if there
is a corresponding data.
Appreciate any guidance, SQL codes or steps.:-)

Kath
 
I assume what you are saying is something like;

If the data field would be let us say "red" you want it to
print "green" instead, otherwise you want it to print the
data as is.
Let us call the data field [colorcode]
The easiest way I think would be to use an IIF statement.

IIF([colorcode] = "red","green",[colorcode])
this would replace data "red" with "green" otherwise it
would print the words in [colorcode]
If you put this in a text box, make sure you insert "="
before the funstion, like:
=IIF([colorcode] = "red","green",[colorcode])
Also make sure the texbox name is something other than the
names of your data fields.
You can also nest this IIF statement, remember the basic
struture of the stement is;
=IIF( condition,if true, if false) separated by "," commas.
Hope this helps.
Fons
 
Thank you for the advice. I tried this and it won't work
for me. what I want is, if [A]=200510, PRINT "1004"; If
[A] = 200520, PRINT "0104", and IF [A] =
2005302, "0304"...etc.

I used =Trim([LAST_NAME] & ", " & [FIRST_NAME] & " " &
[NAME_SUFFIX] & " " & IIf([TERM_CODE_KEY]
-----Original Message-----
I assume what you are saying is something like;

If the data field would be let us say "red" you want it to
print "green" instead, otherwise you want it to print the
data as is.
Let us call the data field [colorcode]
The easiest way I think would be to use an IIF statement.

IIF([colorcode] = "red","green",[colorcode])
this would replace data "red" with "green" otherwise it
would print the words in [colorcode]
If you put this in a text box, make sure you insert "="
before the funstion, like:
=IIF([colorcode] = "red","green",[colorcode])
Also make sure the texbox name is something other than the
names of your data fields.
You can also nest this IIF statement, remember the basic
struture of the stement is;
=IIF( condition,if true, if false) separated by "," commas.
Hope this helps.
Fons
-----Original Message-----
I use Access to make labels, one of which is folder
label. I wonder if I can use "IF...THEN...ELSE" to make
one item on the label to appear as something else if there
is a corresponding data.
Appreciate any guidance, SQL codes or steps.:-)

Kath
.
Click to expand...
.
Click to expand...
 
I would never create an expression like this with IIf(). I would either
create a user-defined function that returns the proper value based on the
passed in [Term_Code_Key].
Function GetCode(plngA As Long) as String
Select Case plngA
Case 200510
GetCode = "1004"
Case 200520
GetCode = "0104"
etc
End Select
End Function

Better yet would be to create a table with a pair of fields that relate one
value to the other. Then you just join the table into your report's
recordsource. You would maintain no complex expressions or code.

--
Duane Hookom
MS Access MVP


Kath said:
Thank you for the advice. I tried this and it won't work
for me. what I want is, if [A]=200510, PRINT "1004"; If
[A] = 200520, PRINT "0104", and IF [A] =
2005302, "0304"...etc.

I used =Trim([LAST_NAME] & ", " & [FIRST_NAME] & " " &
[NAME_SUFFIX] & " " & IIf([TERM_CODE_KEY]
-----Original Message-----
I assume what you are saying is something like;

If the data field would be let us say "red" you want it to
print "green" instead, otherwise you want it to print the
data as is.
Let us call the data field [colorcode]
The easiest way I think would be to use an IIF statement.

IIF([colorcode] = "red","green",[colorcode])
this would replace data "red" with "green" otherwise it
would print the words in [colorcode]
If you put this in a text box, make sure you insert "="
before the funstion, like:
=IIF([colorcode] = "red","green",[colorcode])
Also make sure the texbox name is something other than the
names of your data fields.
You can also nest this IIF statement, remember the basic
struture of the stement is;
=IIF( condition,if true, if false) separated by "," commas.
Hope this helps.
Fons
-----Original Message-----
I use Access to make labels, one of which is folder
label. I wonder if I can use "IF...THEN...ELSE" to make
one item on the label to appear as something else if there
is a corresponding data.
Appreciate any guidance, SQL codes or steps.:-)

Kath
.
Click to expand...
.
Click to expand...
Click to expand...
 
I am interested in your suggestion of creating a new
table - could you please be more specific? Also, if I
were to use the Select case code you suggested, how would
I incorporate it in a file lable report, which files don't
have SQL views, how do I add the function you mentioned?
Thanks again!
Kath
-----Original Message-----
I would never create an expression like this with IIf().
Click to expand...
I would either create a user-defined function that returns
the proper value based on the passed in [Term_Code_Key].
Function GetCode(plngA As Long) as String
Select Case plngA
Case 200510
GetCode = "1004"
Case 200520
GetCode = "0104"
etc
End Select
End Function

Better yet would be to create a table with a pair of fields that relate one
value to the other. Then you just join the table into your report's
recordsource. You would maintain no complex expressions or code.

--
Duane Hookom
MS Access MVP


Thank you for the advice. I tried this and it won't work
for me. what I want is, if [A]=200510, PRINT "1004"; If
[A] = 200520, PRINT "0104", and IF [A] =
2005302, "0304"...etc.

I used =Trim([LAST_NAME] & ", " & [FIRST_NAME] & " " &
[NAME_SUFFIX] & " " & IIf([TERM_CODE_KEY]
<>"200520 ","0104","1004"))
It "OBDC failed", saying it is "not a selected expression."
-----Original Message-----
I assume what you are saying is something like;

If the data field would be let us say "red" you want it to
print "green" instead, otherwise you want it to print the
data as is.
Let us call the data field [colorcode]
The easiest way I think would be to use an IIF statement.

IIF([colorcode] = "red","green",[colorcode])
this would replace data "red" with "green" otherwise it
would print the words in [colorcode]
If you put this in a text box, make sure you insert "="
before the funstion, like:
=IIF([colorcode] = "red","green",[colorcode])
Also make sure the texbox name is something other than the
names of your data fields.
You can also nest this IIF statement, remember the basic
struture of the stement is;
=IIF( condition,if true, if false) separated by "," commas.
Hope this helps.
Fons
-----Original Message-----
I use Access to make labels, one of which is folder
label. I wonder if I can use "IF...THEN...ELSE" to make
one item on the label to appear as something else if
there
is a corresponding data.
Appreciate any guidance, SQL codes or steps.:-)

Kath
.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top