If then Else statement in Report on Open 1

[Bandenjamin]

I have a report where i want the report to look at field1(pulled from QueryZ) and if Field1 = x then Field2 (set as a parameter) = y

What I've tried so far in the on open event

If [field1] = "x" Then [field2] = "y"
ElseIf [Field1] = "y" Then [Field2] = "z"
Elseif [Field1] = "z" then [Field2] = "a"
Else [Field2] = "x"
End if

Can anoyone see where I'm going wrong here? any help is much appreciated.

Dan

 

[dhookom]

You can't reference any values in your code that aren't bound to a control in your report.

It would help to know some context of why you would want to do this. Are you actually try to set a value of a field from the record source? This would never work.

How about helping us out with a justification and explanation of your needs?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Bandenjamin]

I'll see if I can explain it. We have a database that stores reviews for people. There are three people that perform these reviews on a rotating basis. The report is pulling from a query that has the following columns

Analyst (person being reviewed)
Review Date (set to max to report last one)
Reviewer (last person who did review)
Terminated (criteria of false and hidden to remove any old employees)

The report lists all the fields except for "terminated" I'm looking for anyway that I can automate who should do the next review.

Eg.

Analysts
Becky
Sara
Dan

Reviewers
Dave
Bill
Gary

If Becky's last review was done by Gary then her next review should be done by Dave, it if twas Dave then next one by Bill.

Clear as mud?

Thanks,

Dan

 

[dhookom]

I would create a small function that accepts the current reviewer as an argument and returns the next reviewer. This would encapsulate this logic in a single function rather than code in a report.

Public Function NextReviewer(pstrReviewer as String) As String
[green]   'this logic could be replace by references to _
    your table of reviewers if you had a field that _
    identified the order[/green]
   Select Case pstrReviewer
     Case "Dave"
       NextReviewer = "Bill"
     Case "Bill"
       NextReviewer = "Gary"
     Case "Gary"
       NextReviewer = "Dave"
   End Select
End Function

Add this in the query with a column like:
[tt][green]NextReviewer: NextReviewer([Reviewer])[/green][/tt]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Bandenjamin]

Thanks Dhookom, have a star!

Dan