Controlling the control source in a report!!!

[Kckronic21]

HI, I am having problem with my report. I have a report that runs through a form. In this form, I have a list of positons such as Teacher, and Bus Driver. In the control source in my report, I have this code: =[Forms]![Position_Form]![Position_Name_txtbox] & "s". This code puts in the position name in the report header and adds a "s" for postions such as "Teachers" and "Bus Drivers". My only problem is I have some positions that only have one person such as "Cook" and others that do not need a "s" at the end of the position name such as "Sub.". How do I control the control source of a combobox in a report? Do I need to set up some time of If Then statement as well?

Thanks!

 

[ErinB]

Try using a DCount statement

In the code for your command button to open the report try using something along the lines of this:

Dim X as Integer, vsql as String
vsql = " "

X = DCount("*", "Qry behind the report", vsql)
If X > 1
add an S
Else
don't add S
End If

This will count the number of records that should go into the report, if it is greater than one it should print the S after the name.

 

[JoeMiller]

An easier way using DCount, in the control source of your textbox with the s/non-s description put something like this (you'll have to modify the table/query name):

=IIf(DCount("*","QueryFeedingReporting&quot>1,[Forms]![Position_Form]![Position_Name_txtbox] & "s",[Forms]![Position_Form]![Position_Name_txtbox])

HTH Joe Miller
joe.miller@flotech.net

 

[Kckronic21]

Thanks for all of your help. I figured out my problem I just have one other question. Is there a way to also make a position that ha a "y" at the end of the name end in "ies" when there is more than one position such as in "Secretary"? Thanks!