Change Label Caption based on value in table

[jco]

Wondering if anyone can help me out with the following:

Here's the scenario (the database is an issue tracking system)

Tables: "Issues", "Issue_evolution" and "Issue_comments"

The report contains 2 subreports: For each issue I access 1 subreport for evolution and 1 subreport for comments.

Now I have a label for each Issue in the report which defaults to "In progess" I basically need some code to parse through the "issue_evolution" table for that particular issue.id and if the value 10 (Resolved) is found change the label caption to "Resolved".

Any help into this matter would be appreciated.

JCO

 

[stopher]

There's several ways to do this. Here's two...

Method one:
Add a Text Box (not a label) to your report and put the following in the Control Source:

=IIf(DCount("*","Issue_Comments","[IssueID]='" & [IssueID] & "' and [IssueStatus]=10")>0,"Resolved","In Progress")

I've had to guess your field names. Where it says [IssueID]=, this is the name of the issue ID field in the Issues table. Where it says & [IssueID] &, this is the name of the issue ID field in your Issue_Comments table.


Method 2:
This is probably the more efficient and "correct" method imo.
- Create a LEFT JOIN query that joins your current data source with the Issue_Comments table.
- make the IssueStatus available in this query
- add another field that calculates the text:

StatusText:iif([IssueStatus]=10,"Resolved","In Progress")

- use the query as the source of your report and add StatusText to your report

hth
Stopher