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

Assigning text box values in a report using VB versus using nested iif statements...

patjones
Expert 100+
P: 931
Hi:

I have a table in my Access database, "tblWC", which contains a field called "fldTrackingStatus". Furthermore, I have a report "rptTrackingReport" which is based on tblWC (using an SQL query written out in VB). The report sorts all the records out by fldTrackingStatus, which can have an integer value between 1 and 5 inclusive.

The problem is, the report is putting "1", "2", "3", etc. at the top of each tracking status sub-section. I would like to replace those with descriptive text. For instance, instead of "1" the heading for the section containing all records with tracking status 1 would be "Employees out using WC balances".

I have accomplished this by inserting a text box into the tracking status header in report design view, then setting the control source of the text box equal to a bunch of nested "iif" statements:

=IIf([txtTrackingStatus]=1,"Out using WC balances",IIf([txtTrackingStatus]=2,"Receiving grant",IIf([txtTrackingStatus]=3,"Currently on C-status",IIf([txtTrackingStatus]=4,"Recently returned from using WC balances","Recently returned from C-status"))))

My question is whether or not there's a better way for me to accomplish this in the report's VB module, using say a "case" structure. I just want to try and keep things contained in VB as much as possible.

Thank you in advance for any insight you guys can yield.

Pat
Aug 14 '07 #1
Share this Question
Share on Google+
5 Replies


JKing
Expert 100+
P: 1,206
You have posted your question in the Articles section rather than the Forum section.

I have moved it across for you.

MODERATOR.
Aug 14 '07 #2

JKing
Expert 100+
P: 1,206
Why not create a table that stores the values for each of the 5 integers? Then you could include this table in the query and the field storing the status description, This would allow you to simply drag the field onto the report without any code required.

Jared
Aug 14 '07 #3

patjones
Expert 100+
P: 931
Why not create a table that stores the values for each of the 5 integers? Then you could include this table in the query and the field storing the status description, This would allow you to simply drag the field onto the report without any code required.

Jared
I feel like I should've thought of that!

You're suggesting to have a new table with fldTrackingStatus integers as the primary key column and the verbal descriptions in the other column...then establishing a relationship to tblWC via the integers?

I'm going to try it immediately. And thank you for moving my post over. I wasn't paying attention obviously...

Pat
Aug 14 '07 #4

JKing
Expert 100+
P: 1,206
I feel like I should've thought of that!

You're suggesting to have a new table with fldTrackingStatus integers as the primary key column and the verbal descriptions in the other column...then establishing a relationship to tblWC via the integers?

I'm going to try it immediately. And thank you for moving my post over. I wasn't paying attention obviously...

Pat
That's exactly what I'm suggesting. It should work out nicely for you.

Jared
Aug 14 '07 #5

patjones
Expert 100+
P: 931
That's exactly what I'm suggesting. It should work out nicely for you.

Jared
Works great. Thanks!
Aug 14 '07 #6

Post your reply

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