By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

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

P: 1
#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
Expand|Select|Wrap|Line Numbers
  1. =IIf([Day1]=1, "Green")
in first one.
Expand|Select|Wrap|Line Numbers
  1. =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"
Expand|Select|Wrap|Line Numbers
  1. =Count([Day1])
or "if statments"
Expand|Select|Wrap|Line Numbers
  1. =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.
Dec 28 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 30 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
#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
Expand|Select|Wrap|Line Numbers
  1. =IIf([Day1]=1, "Green")
in first one.
Expand|Select|Wrap|Line Numbers
  1. =IIf([Day1]=2, "Red")
in second box etc.
Which makes printout look very bad.
Try this ...

Expand|Select|Wrap|Line Numbers
  1. =IIf([Day1]=1, "Green",IIf([Day1]=2, "Red", IIf([Day1]=3, "Blue")))
  2.  
#2 On a report i have a few textboxes that get data based on "count function"
Expand|Select|Wrap|Line Numbers
  1. =Count([Day1])
or "if statments"
Expand|Select|Wrap|Line Numbers
  1. =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

Expand|Select|Wrap|Line Numbers
  1. 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
Jan 1 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.