Problem access variable in On Format

[Dennis]

Hi,

I'm using Access via Office XP Pro.

I am trying to format the Zip Code on the detail line of my report.

I have tried the following lines of code in both the On Format event and the
On Print event:

If len([PostalCode]) > 5 Then
:
:
end if

or


if len(Me.PostalCode) > 5 Then
:
:
end if

In both events and either code, I receive the error message:
Access can't find the field 'PostalCode' referred to in your expression.

If I put "PostalCode" as the source of the report's control, then every
thing works fine, except the zip code is not formatted.

I tried accessing both Me.PostalCode and [PostalCode] from the immediate
wndow and receive the same error message.

I know that I have not mistyped the field because I copied the field name
from the Source property of the report control. Any idea what is happening?

I also looked at the query which is driving the report. It shows a field
call PostalCode and there is data in the field.

 

[John Spencer]

It would help if you showed the all the code, so we know what you are
attempting to do.

I would keep the control bound to the PostalCode field and I would change the
name of the control to txtPostalCode. And in the section's format event

If Len(Me.txtPostalCode) > 5 Then
Me.txtPostalCode.Format = "@@@@@-@@@@"
Else
Me.txtPostalCode.Format = "@@@@@"
End If

Alternative would be to use an IIF statement in the Control Source of
txtPostalCode.
=IIF(Len([PostalCode])>5,Format([PostalCode],"@@@@@-@@@@"),[PostalCode])

The problem could be that in reports if you don't have a field bound to a
control or used elsewhere (for example, in sorting and grouping) in the report
Access will not return the field when it builds the query for the report and
therefore the field is not available.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dennis]

John,

Thanks for the formatting information.

I forgot that "The problem could be that in reports if you don't have a
field bound to a control or used elsewhere (for example, in sorting and
grouping) in the report
Access will not return the field when it builds the query for the report and
therefore the field is not available. "

Yes, the PostalCode was not bound to a field. It is now and the format
works.