Dynamic report field

[upsman via AccessMonster.com]

Hello. I would like to print a particular value in a report field based on
another field value in a table. For example, if I have a table field called
'campus' and a report field called 'name', I'd like something like this:

if txtCampus = 'north'
print 'john doe' in txtName
else
if txtCampus = 'south'
print 'jane doe' in txtName
else
print 'john smith' in txtName

I'm not sure how to accomplish this. I couldn't seem to find anything on my
report field properties where I would code something like this. Thanks for
any help.

Rod

 

[Guest]

Hi,

You could simply have a field called SOMETHING in your query, let the query
calculate the value that should be in there, and then simply display field
SOMETHING on your report.

eg: select Campus, iif(Campus='North', 'John Doe', iif(Campus='South',
'Jane Doe', 'Invalid Campus')) as SOMETHING from tblCampus

Hope this helps.

Damian.

 

[upsman via AccessMonster.com]

I am not using a query to create the report; I am creating and running the
report by reading the table directly. The report is run when the user clicks
the "Print" button.

Hi,

You could simply have a field called SOMETHING in your query, let the query
calculate the value that should be in there, and then simply display field
SOMETHING on your report.

eg: select Campus, iif(Campus='North', 'John Doe', iif(Campus='South',
'Jane Doe', 'Invalid Campus')) as SOMETHING from tblCampus

Hope this helps.

Damian.

Hello. I would like to print a particular value in a report field based on
another field value in a table. For example, if I have a table field called

[quoted text clipped - 13 lines]

Rod

 

[Marshall Barton]

Hello. I would like to print a particular value in a report field based on
another field value in a table. For example, if I have a table field called
'campus' and a report field called 'name', I'd like something like this:

if txtCampus = 'north'
print 'john doe' in txtName
else
if txtCampus = 'south'
print 'jane doe' in txtName
else
print 'john smith' in txtName

I'm not sure how to accomplish this. I couldn't seem to find anything on my
report field properties where I would code something like this. Thanks for
any help.

Instead of "print", just assign the string to the text box:

Select Case txtCampus
Case "north"
txtName = "john doe"
Case "north"
txtName = "jane doe"
Case Else
txtName = "john smith"
End Select

That code goes in the Format event of the report section
that contains the txtName text box.

With all that said, you would be much better off if you used
a small table with the translation from campus to name.
Then the report record source query could Join the little
table to the table you are currently using. This way you
would have no code so you wouldn't have to find and change
any code when you want to add another direction or modify a
name.