MS Access 2013 Using Expression Builder to only display the first 25 characters in a text field for a report

Question

I use the MS Access 2013 Expression Builder regularly. But one thing I've never managed is simply to show only the first (say 25) characters of of a text field on the report.

It would seem to be a no-brainer. I've done far more complex things in Expression Builder without an issue. But shortening a text field on a report always return #Type! no matter how I try to adjust the expression.

It would seem to me that all I need is: =Left([CompanyName],25)

But it just doesn't work! The report shows a #Type! error.

I can do this no problem by creating a RecordSet SQL string and setting the report RecordSource to it (with OpenArgs). But I can't manage it in Expression Builder.

What am I doing wrong?

Answer 1

When you build expressions for a form, then ANY column that exists in the datasource can be used - EVEN if not placed on the form.

However, reports have a significant difference. Your expressions ONLY work against data bound controls. In other words, the control has to be on the report (no doubt in this case in the details section of the report.

What this means is you have to drop into the details section (and you can and should use in design mode - add existing fields:

So, you need to add the Company name field to the report.

Of course you don't want it to display, so you can delete the label part, and set the control visible part = false. Eg: this:

Remember, you can't have the control reference its self.

In your case, the control you dropped "most" likely is called CompanyName, and then you changing the datasource expression to its self!!!

The other way?

well, you need to RE-NAME your control!!!

If you JUST drop the control onto the report, then its name will be CompanyName.

But, you NOW using =left(CompanyName,25) which POINTS to its self!!!!

So, as noted, you can drop in a company name control and then set visible = false.

The other way, is to CHANGE the name of the control to be DIFFERENT then the company name.

So, you can use add fields, but then in the other tab, rename.

So, say I just dropped in a Description column, but want to use left(10) on it

So, we do this:

Note VERY careful how I re-named the control.

I can't use left(Description,10), since that would refer to the VERY SAME control that I am attempting to use the expression against!!!

I would be saying, please use left of a control called description, but that description s the expression I am wanting to execute against!!!!

So, re-name the control. And now I can do this:

Note the arrow in above - note VERY careful how the control name is DIFFERENT then the data expression (Description) in this example.

Note VERY careful, I can now place OTHER controls on the report, and reference now:

I can reference Description in the expression, OR I CAN USE DescripitonLEFT in that expression!!!!

As you can see, we need a way to distinguish which control we are referencing here. In fact, some developers i have known as a habit NEVER use the same name for their controls vs the datasource of the control for this reason.

In your case the very same expression, and very same control BOTH have the same name - and thus you can't evaluate the expression of a left(Descripiton,10) against the VERY same control with the VERY same name!!!

So, you have to dis-ambiguate the name and the expression you want. In this case CompanyName. As noted, your left("some control") is the VERY same name as your current control.

Try just re-name of the control you dropped into the report.