Report Control Source Manipulation

[justphilip2003]

I am searching for the proper (if possible) control source code to (1) print "Yes" when the table field is "True" and use case values 1 to 5 to insert a different phrase for each number. I gather that "If Then logic" isn't used in "Expression Builder"! Also is there a similar technique for Queries? TIA Phil

[Beetle]

print "Yes" when the table field is "True"

If this is a boolean (Yes/No) field in your table then a simple IIf statement should suffice;

IIf([YourField]=0, "False, "True")

values 1 to 5 to insert a different phrase for each number.

You could use a nested IIf statement here;

IIf([YourField]=1, "Text 1", IIf([YourField] = 2, "Text 2", IIf([YourField]=3, "Text 3"....and so on

However, nested IIf statements can become a bit cumbersome and difficult to read when you have several conditions. A couple of other options;

The Choose function, which simply chooses the value based on it's position in the list as compared to the index value (which in this case would be the value 1 - 5 in your field).

Choose([YourField],"Text 1","Text 2","Text 3","Text 4","Text 5")

Or, the Switch function, similar to the Choose function but it takes pairs of arguments;

Switch([YourField]=1,"Text 1",[YourField]=2,"Text 2",[YourField]=3,"Text 3",[YourField]=4,"Text 4",[YourField]=5,"Text 5")

[justphilip2003]

Thanks for your response. I managed to code the Iff function with no problem. however when I attempted to use Choose or Switch functions I get a string error or syntax "comma" error here is my code
=Switch([PoliticalPreference]=1,"Democrat",[PoliticalPreference]=2,"Independent",[PoliticalPreference]=3,“NonVoter",[PoliticalPreference]=4,"Tea Party",[PoliticalPreference]=5,"Republican")

=Choose([PoliticalPreference],"Democrat","Independent","[NonVote]","[Tea Party]","Republican”) . The Table has values 1 for Democrat, 2 for Independent, etc. I placed spaces after comma with no change. To my untrained eye, my code appears correct. Where am I errors? TIA Phil

[justphilip2003]

I’ve tried the examples shown below and I always get syntax errors or a window asking for choice number. Please what am I doing wrong?
=Choose([PoliticalPreference],"Democrat","Independent","Non Voter","Tea Party","Republican”)
=Choose([PoliticalPreference],"Democrat","Independent","[Non Voter]","[Tea Party]","Republican”)
=Choose([PoliticalPreference],"Democrat” 1,"Independent” 2,"Non Voter” 3,"Tea Party” 4,"Republican” 5)
=Choose([PoliticalPreference],"Democrat 1”,"Independent 2”,"Non Voter 3”,"Tea Party 4”,"Republican 5”)
TIA Phil

[rzw0wr]

You may want to try the Case Select.

Select Case Combo0
    Case 1
         something
     Case 2
        something different
End Select

Dale

[justphilip2003]

Thanks for the idea, but it resulted in a message box requesting a parameter value. I placed this code in the report field Control Source:
=[Select Case Combo() Case 1 "Democrat" Case 2 "Independent" Case 3 "Non Voter" Case 4 "Tea Party" Case 5 "Republican" End Select ]
Any suggestions? TIA Phil

[rzw0wr]

Sorry Philip.

This code should be in an event.
Combo0 should be replaced with your combo box or whatever you are using to select your data.

I would make a combo box to hold your selections and put this code on the After UpDate event.

Let us know if your are still having problems.

Maybe upload your database would help.

Dale

[justphilip2003]

Thanks for the response! This field is in a report. Can a report field access an event procedure? I tried {=[Select Case PoliticalPreference() Case 1 "Democrat" Case 2 "Independent" Case 3 "Non Voter" Case 4 "Tea Party" Case 5 "Republican"]} in the field control source and on the attempted execution of the report I get the usual message window asking to "Enter Parameter Value." I really don't want to place the text in another table route, that logic I have used successfully before; I'm trying to learn new techniques and broaden my understanding, like with Iffs, Choose and Switch. Always a syntax error with these functions. Phil

[rzw0wr]

Philip,
How are you opening this report?

Dale

[justphilip2003]

After making a corrective change to the Control Source code, I press enter to go to the next property entry (If Access allows me to do this, the code is saved in the control Source changes are saved. I then click on the Print Preview tab. No report preview has every occurred with selective code in the property control source. Phil

[rzw0wr]

After making a corrective change to the Control Source code, I press enter to go to the next property entry (If Access allows me to do this, the code is saved in the control Source changes are saved. I then click on the Print Preview tab. No report preview has every occurred with selective code in the property control source.

Philip, I have no idea what you are talking about.


Dale

[justphilip2003]

If the code has a syntax error, Access will not save it and allow you to open the report (switch Views). Since the report is in development stage, I switch between Design and Execute window through the View Tab. Phil

[rzw0wr]

Philip,
How are you opening this report?

Dale

FROM where are you trying to open this report?
A form? What?
You have lost me completely.

Dale

[ssanfu]

In post #3, there is an incorrect character.The double quote in front of Nonvoter shouold be replaced (in red):

=Switch([PoliticalPreference]=1,"Democrat",[PoliticalPreference]=2,"Independent",[PoliticalPreference]=3, “NonVoter",[PoliticalPreference]=4,"Tea  Party",[PoliticalPreference]=5,"Republican")

It should be:

=Switch([PoliticalPreference]=1,"Democrat",[PoliticalPreference]=2,"Independent",[PoliticalPreference]=3,"NonVoter",[PoliticalPreference]=4,"Tea  Party",[PoliticalPreference]=5,"Republican")

[justphilip2003]

Than you so very much! I could not see the forest for all of the trees! LOL Small print is hard for me to see. Phil