asked on

MS Access If statement on a report

Hello. I have a very simple db (ms access for office 365). I created a report in order to see how I want to arrange the data. The report is based off a query. I have three test records. One of my fields on the report is called "AttributeValue". I then created an unbound text box and called it Pdesc. What I am trying to do is build a simple if statement. I placed the following statment on in the On Load event procedure of the report:

If [AttributeValue] = "36" Then
[PDesc] = "Test"
End If

When I preview the report my unbound text box does not populate if the condition has been is met. What am I doing wrong?

Jim Dettman (EE MVE)

<< On Load event>>

You want this in the OnFormat of the section where the control is.

OnLoad only fires once at the start of the report, not for each record.

Jim.

DJ P

ASKER

Thanks. The good news is my unbound text box is populated. However, Its populating for all records regardless of the value in my attributevalue field.

Norie

If AttributeValue and PDesc are the names of a controls/fields on the form you need a reference to the form itself to get the value.

If Me![AttributeValue] = "36" Then
    Me![PDesc] = "Test"
End If

Open in new window

DJ P

ASKER

Tried that as well. Same result. See attached if that helps
PriceList.zip

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

I would actually do that in the query that is the recordsource for the report:

Assuming AttributeValue is numeric:

SELECT Field1, Field2, ..., FieldN, IIF([AttributeValue] = 36, "Test", "") as Expr1 FROM your table

Open in new window

If AttributeValue is a string:

SELECT Field1, Field2, ..., FieldN, IIF([AttributeValue] = 36, "Test", "") as Expr1 FROM your table

Open in new window

And if you are going to have multiple options, you might consider using the Switch( ) function, which allows you to provide pairs of values, the first being an expression, the next being the value to return if the expression is true. That would look something like:

SELECT Field1, Field2, ..., FieldN
, Switch("[AttributeValue] = 36", "Test 36", "[AttributeValue] = 37", "Test 37", True, "Unknown Value") as Expr1 
FROM your table

Open in new window

Notice that my final pair of arguments (True, "Unknown Value") are included so that if none of the other expressions evaluate to True, then the final pair will give you a value.

Jim Dettman (EE MVE)

<< I would actually do that in the query that is the recordsource for the report:>>

I would disagree with Dale here; IIf() is not something I like to put in a query, but I would put this in a query by creating a description lookup table and and joining to it.

IIF()'s are very hard to write, troubleshoot, and maintain when they get more than a level or two deep. And with things like Switch(), Replace(), etc, again they can be difficult to write and maintain. Also some of these are VBA calls, which are not optimizable by the query processor and if you every move to a SQL backend, force a query to execute locally.

For something like this, you'd be far better off to do a lookup table and then a join to your main table.

Jim.

Dale Fye

Well, Jim, he did only give one example.

So doing an IIF() or a Switch() would work for a relatively small set of AttributeValue values. But I would agree that a lookup table with AttributeValues and their descriptions would be far more efficient.

Dale ;-)