Problem access variable in On Format

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.


-- 
Dennis
0
Utf
2/3/2010 7:02:01 AM
access.reports 4434 articles. 0 followers. Follow

2 Replies
841 Views

Similar Articles

[PageSpeed] 40

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 wrote:
> 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.
> 
> 
0
John
2/3/2010 1:29:10 PM
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.  

-- 
Dennis



0
Utf
2/3/2010 4:30:06 PM
Reply:

Similar Artilces: