automaticaly change a part of expression in a number of a report's control values

[pripa]

#1 in my report i need to group my clients and print a color code "Green", "Red" and "Blue" above names of the clients depending on values in the order form. Is it possible to print it from the single textbox (right now I have 3 textboxes with

=IIf([Day1]=1, "Green")

in first one.

=IIf([Day1]=2, "Red")

in second box etc. Which makes printout look very bad.

#2 On a report i have a few textboxes that get data based on "count function"

=Count([Day1])

or "if statments"

=IIf([Day1]=1, "Green")

where "Day1" is a field in a query. This field represents a day of a month. And for dayly report i have to change it every day replacing "Day1" with "Day2" etc in 5 textboxes.
How can I automate changing these field names inside functions and "if" statements.

[NeoPa]

Pripa - try reposting the questions but remember we don't know anything until you tell us. Read through the question before you hit submit and see if it makes sense to you. If not, then we, who don't know what happens in your database, will be even more confused.

MODERATOR.

[MMcCarthy]

#1 in my report i need to group my clients and print a color code "Green", "Red" and "Blue" above names of the clients depending on values in the order form. Is it possible to print it from the single textbox (right now I have 3 textboxes with

=IIf([Day1]=1, "Green")

in first one.

=IIf([Day1]=2, "Red")

in second box etc.
Which makes printout look very bad.

Try this ...

=IIf([Day1]=1, "Green",IIf([Day1]=2, "Red", IIf([Day1]=3, "Blue")))
 

#2 On a report i have a few textboxes that get data based on "count function"

=Count([Day1])

or "if statments"

=IIf([Day1]=1, "Green")

where "Day1" is a field in a query.
This field represents a day of a month. And for dayly report i have to change it every day replacing "Day1" with "Day2" etc in 5 textboxes.
How can I automate changing these field names inside functions and "if" statements.

Have an invisible textbox on the report called dayNum and set control source to
=[Enter Day Num:]
this will prompt you to enter the day number when the report opens

Now all these textbox control source expressions will have to be set using vba in the reports on format event

Me.textboxName.ControlSource = "=IIf([Day & Me.dayNum & "]=1, "Green",IIf([Day & Me.dayNum & "]=2, "Red", IIf([Day & Me.dayNum & "]=3, "Blue")))

And so on ....

Mary